Well the title is kinda misleading but i don't know how to phrase it in a small description like that.
I need a query that will go through a table and find the 2 most used fkListingId's and return those 2 fkListingId's. Like if I have a table with rows:
Id, fkListingId, fkUserId
with values
1, 4, ...
2, 4, ...
3, 1, ...
4, 6, ...
5, 4, ...
6, 1, ...
7, 9, ...
8, 1, ...
9, 4, ...
10, 5, ...
notice 4 and 1 are the most common with 4 being more common than 1. I want to return Id's 4 and 1 with maybe a count with it like return:
4 => 4 (the count)
1 => 3 (the count)
now I have no idea what to use in this query so any help is appreciated.
Get most common fields
Moderator: General Moderators
Code: Select all
SELECT fkListingId, count(fkListingId) as total FROM table_name GROUP BY fkListingId ORDER BY total DESC
EDIT: You can add "LIMIT 2" to the end of it if you only need the top two results, but this means that if you have, say, 4 results that all have a count of 5, you'll lose two of them.