Select top record from group

Question

How to from this table

Name  Code
ABC     Code1
DEF     Code1
GHI     Code2
JKL     Code2
OMN     Code3

get this result:

Name  Code
ABC   Code1
GHI   Code2
OMN   Code3

Is there any simple solution?

Answer 1

I considered that you want your results to be alphabetical.

SELECT
         MIN(Name), Code
FROM 
         MyTable 
GROUP BY 
         Code

Answer 2

SELECT MIN(c1), c2 FROM table GROUP BY c2 ORDER BY c2 ASC;

Answer 3

SELECT MIN(Name),Code
FROM Table
GROUP BY Code

Answer 4

if your "TOP" is defined by "first in alphabtetical order", you could simply use a simple group by clause with the MIN aggregation.

SELECT MIN(Col1), Col2 FROM table GROUP BY Col2

otherwise, you will need another column, such as an incrementing uniqueid or a creation date.