Page 1 of 1

Get most common fields

Posted: Wed Jul 26, 2006 7:14 am
by shiznatix
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.

Posted: Wed Jul 26, 2006 7:49 am
by GM

Code: Select all

SELECT fkListingId, count(fkListingId) as total FROM table_name GROUP BY fkListingId ORDER BY total DESC
should be pretty much what you want.

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.

Posted: Wed Jul 26, 2006 8:19 am
by shiznatix
thanks much, ill play around with that