CODEWITHSUNDEEP
sqldatabasesqlite
LuckyLuke
LuckyLuke

Reputation: 49077

Using max function on the resultset of count

==========================  
    uid     |     tid
==========================
     1      |      0
     1      |      1
     1      |      2
     2      |      1
     2      |      2
     3      |      2
     4      |      3
     4      |      0
     4      |      4

etc..

This is the "join-table" in my many-to-may relationship. What I want to do is to count 'tid' (grouped). Then I want to find the highest count of 'tid'. When I have done that I want to use that 'tid' to join it with the lookup-table (9 rows with the tid as primary and description of that category)

What I have written so far is:

select tid, max(count) from (select tid, count(tid) as count from klb_log_food_maps group by tid);

The count returned is correct however the 'tid' is not correct, seems like it is the last tid in that table.

Upvotes: 0

Views: 157

Answers (1)

Dan Breen
Dan Breen

Reputation: 12924

You could avoid the subquery by ordering on the count:

select tid, count(tid) as count
from klb_log_food_maps
group by tid
order by count desc
limit 1;

Upvotes: 2

Related Questions