How to select distinct value from one column only

Question

I have records as follows:

key  | name
--------------
1111 | aa   
1111 | bb    
2222 | cc

I need to select the key and name when the key value is distinct. When I tried:

select distinct key, name from table;

I got all the rows since the query takes distinct for the combination of the columns: key and name. But, what I need is only distinct key and I don't care about the name. I have a lot of records, so I need a practical method.

score 12 · Accepted Answer · answered Dec 12 '12 at 20:42

12

Query:

SELECT `key`, MAX(`name`) as name
FROM `table`
GROUP BY `key`

answered Dec 12 '12 at 20:42

Justin

9,634
6
35
47

score 3 · Answer 2 · answered Dec 12 '12 at 20:45

3

Why not just:

SELECT distinct key
FROM table

or

SELECT key, name
FROM table
GROUP BY key

answered Dec 12 '12 at 20:45

fthiella

48,073
15
90
106

score 1 · Answer 3 · answered Dec 12 '12 at 20:43

SELECT key, name FROM table GROUP BY key;

This returns one row for each distinct value of key, and the value of name is arbitrarily chosen from the rows in that group. In practice, MySQL tends to return the value of name from the row physically stored first in the group, but that's not guaranteed.

As other answers show, you can put name into an aggregate expression.

amk · Answer 4 · 2012-12-12T21:45:26.357

0

If you do not care about not groupped fields try this query -

select key, name from table group by key order by name

MySQL lets selecting fields without using aggregate function.

order by name helps to select first name from the group.

edited Dec 12 '12 at 21:45

answered Dec 12 '12 at 20:44

amk

284
1
3

I believe this is invalid SQL, altough MySQL accepts it and returns something. Also this "something" is a random name from the set of names having the same key, therefore the result is not deterministic (depending on table deletions and inserts). This makes no sense and encourages misunderstanding among those that do not already grasp the concept of aggregation. – colemar Dec 12 '12 at 21:34
I cannot say it is invalid. MySQL allows it, and we can use it. I agree about random record; there should be aggregate function, or ORDER BY clause - which will help to select exact value. I will edit my answer. – amk Dec 12 '12 at 21:44
It is invalid per ANSI/ISO SQL, since it results in an ambiguous result. MySQL and SQLite are the only RDBMS implementations that permit this form, as far as I know. Other RDBMS products return an error. – Bill Karwin Dec 12 '12 at 22:36

score 0 · Answer 5 · answered Dec 12 '12 at 20:47

0

How about this:

select * from table group by key having count(key) = 1

answered Dec 12 '12 at 20:47

systemboot

860
2
8
22

How to select distinct value from one column only

5 Answers5

Linked