Select all columns with GROUP BY one column

Question

I have this table:

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

Is there a 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 group by all the columns that I need to show.

Is there a solution for this problem?

You need an aggregate function to use group by. What's your aggregate function? — Bakhtiar Hasan, Oct 02 '16 at 10:36
What aggregate function you required? Mean count or min or max or anything else — Jim Macaulay, Oct 02 '16 at 10:39

juergen d · Answer 1 · 2016-10-02T10:45:44.153

23

A query that works for all DB engines would be

select t1.*
from sch.mytable t1
join
(
    SELECT min(id) as id
    FROM sch.mytable 
    GROUP BY key
) t2 on t1.id = t2.id

where min(id) is the function that influences which result you get. If you use max(id) you get the other.

edited Oct 02 '16 at 10:45

answered Oct 02 '16 at 10:42

juergen d

195,137
36
275
343

For others: In this example 'id' is the 'primary key' column of mytable. (I may be wrong, but That's what I assumed and query worked fine) – supernova May 25 '17 at 20:45
Just to note, `join` is the same thing as `inner join` as depicted [here](https://miro.medium.com/max/1200/1*YhYiJJnQLr5Z7PBKNakN2Q.png) – quasipolynomial Jul 17 '19 at 11:14

score 11 · Accepted Answer · answered Oct 02 '16 at 10:38

11

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.

answered Oct 02 '16 at 10:38

Clodoaldo Neto

108,856
25
211
247

Select all columns with GROUP BY one column

2 Answers2