MSSQL Top count

I would like to write a query that will return the most unique records. For example...I have a table that is keeping track of each time a photo is viewed. It is revolving, so there is also a script to delete views after a week. So essentially the table is two columns, PhotoID and ViewTime. What would be a good SQL query to return the photoID with the most records (views)? Or is it easier to run it in a PHP while statement?

Thanks!

Matthew

 

do you have a field that holds the count?

 

NO, that is what I am trying to determine. I have two fields in the table. PhotoID and the time it was viewed. A script comes thru and deletes all records older than a week. So, I would like to count the number of times each PhotoID is listed, and rank them in order from most to least.

 

I would add a field to the table called ViewCount and write little code that checks the current count and updates it by +1 whenever someone views it.

 

Unfortunatly that will not work. I am trying to do a running count....so any views over a week old is deleted. I need to keep track of each vew separatly so I can delete them after 1 week.

 

make another table with a foreign key of the image id (and/or other image information) and then put the viewcount in there.

 

I found the real answer. Thanks for the help!

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

 

odd, becuase your question has nothing to do with your solution.