SQL Derby: GROUP BY not selected id

I 开发者_如何学Pythonam using Derby.

I have a table with two columns X and Y.

I want to select column X but group by column Y.

This means I want to show only ONE X value for each row with the first Y value to it.

How can I do this?

CREATE TABLE test (
    x INTEGER  NOT NULL ,
    y INTEGER  NOT NULL )

INSERT INTO test VALUES (1,1) ,(1,2) ,(1,3) ,(2 ,3),(3,3)

SELECT y FROM test GROUP BY x -- does not work 

i want all y values with no duplicate

Raw Data

X    Y
--   --
1    1
1    2
1    3
2    3
3    3

The results should be:

X    Y
--   --
1    1
1    2
2    3

You've got to aggregate x in some fashion:

SELECT   Y,
         MAX(X) AS Max_X,
         AVG(X) AS Avg_X

FROM     myTable
GROUP BY Y
ORDER BY Y

Minimum, Maximum, Average, First, Last, etc.

If you don't care which x you get, perhaps just choose the first or last:

SELECT   Y,
         First(X) AS X,
FROM     myTable
GROUP BY Y

You must specify which X you want.

select
  min(X) X
from
  table
group by
  Y

I’m not familiar with Derby, but try a sub-query like:

SELECT X
FROM table
WHERE Y in (SELECT MIN(Y) FROM table)

Here SELECT MIN(Y) FROM table is to select the value for Y (i.e. your “first Y value”, here: the minimum of Y).