Displaying additional information when GROUPing in SQL

Question

This question is inspired by this problem set (extension of problem 11).

I have a relational database, which consists of three tables, which are connect accordingly:

bands (id, name)

albums (id, name, release_year, band_id)

songs (id, name, length, album_id)

The primary and foreign keys are what the column's names suggest.

I want to select the longest song of each album and display the album, the release year, the song's duration and its name. If I omit the last column, this query does the job:

-- Select the longest Song of each Album --
SELECT
     albums.name AS 'Album',
     albums.release_year AS 'Release Year',
     MAX(songs.length) AS 'Duration'
FROM albums
JOIN songs ON songs.album_id = albums.id
GROUP BY albums.name, albums.release_year;

However, when grouping, the name column cannot be displayed, because it's not part of the GROUP (because each song name is unique and then no elements will be grouped) or of an aggregate function.

Is it possible do display the name of the longest song as well and, if yes, what should I add to the query?

Answer 1

Instead of MAX and GROUP BY you can use a ROW_NUMBER() and then filter:

WITH agg AS
(
  SELECT 
    a.name         AS Album,
    a.release_year AS [Release Year], 
    s.name         AS Song, 
    s.duration     AS Duration,
    r = ROW_NUMBER() OVER (PARTITION BY s.album_id ORDER BY s.duration DESC)
  FROM dbo.albums AS a
  INNER JOIN dbo.songs AS s 
  ON s.album_id = a.id
)
SELECT Album,
       [Release Year],
       Song,
       Duration
FROM agg
WHERE r = 1;