Select all columns with GROUP BY one column [duplicate]

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?

Answer 1

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.