Select top 5 "count" from MySQL

[ssanders82]

Hi there. I have a classified ads site, and each time a person views an ad, I add a row to my Listing_View table with some other info and the listing_id as foreign key. Now, I know how to, for instance, get the number of views for listing_id = 'xxx', but on my home page I want to display the top 5 (or 10) listings that have the most views. I looked in the MySQL manual and couldn't find anything about it, and I can't figure out how to use SELECT COUNT to do this, so I guess I have to write a PHP function. Or would it be something along the lines of "SELECT MAX(COUNT(*)) FROM Listing_View LIMIT 0,5"? I know that doesn't work, but can I do it with SQL? Anyone ever done this before? Thanks all.

[ssanders82]

Oh, one more thing...should I just keep a column called "num_times_viewed" in my Listing table and increment it once each time it is viewed? That seems kinda redundant because I also want to keep the Listing_View table to track browser type, etc.

thanks

[kristianblom]

SELECT listing_id, count(views) AS amount FROM Listing_View GROUP BY listing_id ORDER BY amount DESC

[scoppc]

I personally prefer to use numberOfView field that increments everytime it's viewed.

This could reduce the amount of processing needed to display the TOP 10 or TOP 5 list.