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!

Solution 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.

Solution 2

Try this

SELECT T1.id, T1.streetname FROM TableName T1
INNER JOIN 
(
SELECT id, MAX(count) maxcnt FROM TableName
GROUP BY id
) T2 
ON T1.id= T2.id AND T1.count = T2.maxcnt

SQL FIDDLE DEMO

Comments

[BigMike]

SELECT ID, STREETNAME FROM TABLE ORDER BY COUNT DESC, then fetch just one row

[Taryn]

Welcome to Stack Overflow! Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it.

[Gordon Linoff]

What do you mean by count? The number of rows? The sum of the count field? The count field itself?

[user2721421]

Thanks for your comments! I'm not familiar with SQL, but there was no other way to solve this kind of problem... I just exported the table from ESRI ArcGIS to Microsoft Access.

[OutOfSpaceHoneyBadger]

He didn't specify how many records he wants to select, didn't he? I made this answer just to select id and streetname ordered by count. If I misunderstood the question, I apologize.

[user2721421]

Thanks, this is exactly what I needed! But now there is another issue: All ID's that have the same max value should not be displayed, because they are not unique...

[Taryn]

@user2721421 I'm not sure what you mean, but if you have multiple ids with the same streetname and count, then you should be able to use distinct. Can you edit the sql fiddle (sqlfiddle.com/#!2/17dae/1) with some more sample data to further explain the problem?

[user2721421]

I changed the SQL Fiddle (sqlfiddle.com/#!2/86465/1/0) to my needs. The ID and the count is the same, but the street is different. ID 3 should be eliminated in the result, so that there are only the unique IDs 1 and 2. About ID 3 I cannot make any statement, because the result would be weighted the same. By the way SQL Fiddle is a nice tool!

[Taryn]

@user2721421 What database are you using? So if you have two Ids with the same count you do not want it returned?

[Taryn]

@user2721421 Without knowing what database you are using, how about using something like this? -- sqlfiddle.com/#!2/86465/9

[user2721421]

Yes, that seems to work... =) Thanks a lot! I'm working with MS Access at the moment.

[Taryn]

@user2721421 Happy to help!