Hello, I am trying to set up a string where it taps two databases, Albums and Photos. I need to select the following columns of Albums database and Photos where the Albums.AlbumType='Nightlife' and then order by the sum of photos.views desc for ranking the top 4 (limit 4)

This is what I have but, obviously does not work:

"SELECT Albums.AlbumLabel, Albums.AlbumID, Albums.DateCreated, Albums.AlbumDate, Albums.AlbumLocation, Albums.AlbumEvent FROM Albums, Photos WHERE Albums.AlbumType='Nightlife' ORDER BY SUM(Photos.Views) DESC LIMIT 4"

Now, in the Photos database, each photo has a photoid, albumid to match the album, and a views column. I need to basically add all the views together to get a sum for each albumid then rank the top 4. Then pull the album information for displaying. Been working on this awhile and can't seem to get it to work. Thanks.

oh, and I am very new to the row_number() method, so if I could put the row number of each one on the site (ranked #1, #2, #3, #4) without having to use a separate function, that would be great.

Nevermind, figured it out. Thanks anyway:

"SELECT Albums.AlbumLabel, Albums.AlbumID, Albums.DateCreated, Albums.AlbumDate, Albums.AlbumLocation, Albums.AlbumEvent, SUM(Photos.Views) FROM Albums, Photos WHERE Albums.AlbumType='Nightlife' and Albums.AlbumID=Photos.AlbumID GROUP BY Photos.AlbumID ORDER BY SUM(Photos.Views) DESC LIMIT 4"
This article has been dead for over six months. Start a new discussion instead.