CODEWITHSUNDEEP
mysqlsql
Cyprus106
Cyprus106

Reputation: 5780

Selecting records in order of the number of records that contain a certain field

We have a table that is arranged as such: ID, Album_ID and Time_Voted. Each time a user votes, it creates a new record. We'd like to display the top five Album_IDs, (which had the most records containing that Album_ID at the end of every week). The only way I know how to do this is to SELECT all records from the past week, then run a "while" on the results,then iterate through an array and and add a +1 to the corresponding Album_ID key each time it finds an instance of the same Album_ID. This is quite inefficient, and I'm convinced there's a good way to do this via SQL query, but it's beyond my level of knowlege.

So the question is, is there a way to, via query, get a count of each number of album_IDs, and then arrange by that count?

Here's the original SQL statement I was working with. The YEARWEEK stuff was just to get last week. 

SELECT * FROM wp_album_votes WHERE YEARWEEK( Time_Voted ) = YEARWEEK( CURRENT_DATE - INTERVAL 7 DAY ) ORDER BY Album_ID

Upvotes: 0

Views: 97

Answers (2)

Randy
Randy

Reputation: 16673

select album_id, count( album_id ) 
from wp_album_votes
group by album_id
order by 2 desc.

you can wrap your other text and dates onto this basic structure...

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135868

SELECT Album_ID, COUNT(*) AS NumVotes 
    FROM wp_album_votes 
    WHERE YEARWEEK( Time_Voted ) = YEARWEEK( CURRENT_DATE - INTERVAL 7 DAY ) 
    GROUP BY Album_ID
    ORDER BY NumVotes DESC LIMIT 5

Upvotes: 6

Related Questions