0

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.

1
Contributor
2
Replies
3
Views
9 Years
Discussion Span
Last Post by SheSaidImaPregy
0

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.

0

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 topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.