SQL SELECT MAX COUNT

Question

I have three columns in a table: id, streetname, count. To some ids is more than one streetname assinged. Count tells how often the respective street is assigned to the id. How can I get just the id and the streetname with the highest count.

Example table:

id     streetname     count
1      street1        80
1      street2        60
1      street3        5
2      street4        10
2      street5        6

Result should be like this:

id     streetname
1      street1
2      street4

Thanks in advance!

Answer 1

You did not specify what database you are using but you should be able to use the following:

select t1.id, t1.streetname, t1.count
from yourtable t1
inner join
(
  select id, max(count) max_count
  from yourtable
  group by id
) t2
  on t1.id = t2.id
  and t1.count = t2.max_count

See SQL Fiddle with Demo. Note, you will have to escape the count column name using backticks for MySQL or whatever character your database uses to escape reserved words. My suggestion would be to avoid using reserved words for column and table names.