Youcef Laidani Youcef Laidani - 2 months ago 7
SQL Question

Select all columns with GROUP BY one column

I have this table :

+----+-----+----------+
| id | name| key |
+----+-----+----------+
| 1 | foo |111000 |
| 2 | bar |111000 |
| 3 | foo |000111 |
+----+-----+----------+


Is there any way to Group by the key to get this result?

+----+-----+----------+
| id | name| key |
+----+-----+----------+
| 2 | bar |111000 |
| 3 | foo |000111 |
+----+-----+----------+


Or this result:

+----+-----+----------+
| id | name| key |
+----+-----+----------+
| 1 | foo |111000 |
| 3 | foo |000111 |
+----+-----+----------+


If i use this query:

SELECT * FROM sch.mytable GROUP BY(key);


This is not correct i know that, because i should to group by all the column that i need to show.

Is there any solution for this problem?

Answer

distinct on

select distinct on (key) *
from t
order by key, name

Notice that the order by clause determines which row will win the ties.