user6492999 user6492999 - 3 months ago 7
SQL Question

How to select group by 2 columns + id?

I have a problem with data selection using SQL in PostgreSQL database.

I have the following data in one table:

ID ID_X ID_Y
100 1 2
101 1 1
102 1 1
103 1 2
104 5 10
105 5 11
106 5 10
107 5 11
108 8 20
109 8 30
110 8 20


How to write select statement to get the following results?

ID ID_X ID_Y
100 1 2
101 1 1
104 5 10
105 5 11
108 8 20
109 8 30


I know that it is a kind of group by ID_X and ID_Y but how to select also "ID" column without grouping by it?

Maybe there is a way to select using distinct? or group by with subselect? Please help :)

Answer

You can use an aggregate function like MIN() or MAX(). In your case you want MIN() to get those specific results.

SELECT MIN(ID), ID_X, ID_Y
FROM [tablename]
GROUP BY ID_X, ID_Y
Comments