Count duplicate records

[MSteinblock]

I have a script that adds a record each time a photo is viewed, as well as the time it was viewed. I am doing it that way as I want to be able to delete any records over 1 week old. What I would like to get out is the highest views. Basically it would look at all the records and tell me which photo has the most views. Is there a way to count duplicate records and sort descending?

Thanks for the help!

Matthew

[mrhappiness]

search for COUNT, GROUP BY and ORDER BY on http://dev.mysql.com/doc/mysql/en/

[MSteinblock]

Thanks for the help. Does this still pertain if I am using MSSQL rather than MySQL? I have never used some of these functions, can I place them all in one query? When I did searchs for them, there are only 1000 different ways to use each function. 🙂

Matthew

[mrhappiness]

SELECT COUNT(view_id)
FROM tablename
GROUP BY photo_id
ORDER BY COUNT(view_id) DESC

should work

if not, try ORDER BY 1 DESC

and view the result in your query analyzer

[MSteinblock]

Thanks VERY MUCH for the help!!!! This is what I ended up using:

SELECT TOP 1 PhotoID FROM RecentViews GROUP BY PhotoID ORDER BY COUNT(PhotoID) DESC

Thanks again!

Matthew