Query to display top rated artist albums in MS Access

I have two tables in a music db. [Artists] has PK artist_id and artist_name. [Albums] has FK artist_id, album_id, album_name, and rating.

I want to select MAX(rating), album_name and GROUP BY [album].artist_id. Access prevents me from displaying the album_name because it must be included in the GROUP BY function in addition to the artist_id which defeats the point.

My query:

SELECT albums.artist_id, MAX(albums.rating) 
FROM Albums 
GROUP BY albums.artist_id;

The above query selects the top album for each artist but does not include the name of the album. I’ve also attempted JOINs to get the artist_name to display with no luck.

Could you please advise me on how I can select the required fields? EDIT: I got it working with this query:

    SELECT Album_name, rating, Artists.artist_name
    FROM Albums
    INNER JOIN Artists ON Albums.artist_id = Artists.Artist_id
    WHERE rating = (select MAX(rating) from Albums i 
    WHERE i.artist_id = albums.artist_id)

Answer

There are many ways to achieve this –

---

Using a joined subquery:

One possible method is to use an inner join on a subquery that selects the maximum rating for each artist_id:

select t1.*
from albums t1 inner join
(
    select t2.artist_id, max(t2.rating) as mr
    from albums t2
    group by t2.artist_id
) q 
on t1.artist_id = q.artist_id and t1.rating = q.mr

Note that this will return multiple records if two or more albums share the same rating.

---

Using a correlated subquery:

Another method is to use a correlated subquery which attempts a select a record with a greater rating than the current record, and return the records for which such subquery returns no results (represented by the where not exists clause):

select t1.*
from albums t1 where not exists 
(
    select 1 from albums t2 
    where 
    t1.artist_id = t2.artist_id and t1.rating < t2.rating
)

---

Using a LEFT JOIN with unequal join criteria:

Finally, you could also use unequal join criteria with a left join in the following way, returning records for which there are no records on the right of the join which meet the join criteria:

select t1.* 
from 
    albums t1 left join albums t2 on 
    t1.artist_id = t2.artist_id and t1.rating < t2.rating
where 
    t2.rating is null

This example can only be represented in MS Access in the SQL view, as the MS Access Query Designer cannot display joins which have equal join criteria (i.e. where one field equals another).

This example is similar in operation to the correlated subquery, but the selection is performed by the join, rather than within the WHERE clause.