How to select a single value from only one column

Question

I have entries as follows:

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

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

 select distinct key, name from table; 

I got all the rows since the query is different from the combination of columns: key and name . But I only need a great key , and I don't care about name . I have a lot of notes, so I need a practical method.

Answer 1

Query:

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

Answer 2

Why not just:

 SELECT distinct key FROM table 

or

 SELECT key, name FROM table GROUP BY key 

Answer 3

 SELECT key, name FROM table GROUP BY key; 

This returns one row for each individual key value, and the name value is arbitrarily selected from the rows in this group. In practice, MySQL tends to return the value of a name from a string physically stored first in a group, but this is not guaranteed.

As the other answers show, you can put the name in an aggregated expression.

Answer 4

If you don't care about ungrouped fields, try this query -

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

MySQL allows you to select fields without using an aggregate function.

order by name helps you choose a name from the group.

Answer 5

How about this:

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