Get most common fields

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Get most common fields

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

thanks much, ill play around with that
Post Reply