CODEWITHSUNDEEP
sqldatabasepostgresqljoin
1in7billion
1in7billion

Reputation: 341

Join table to subquery on another table and then sort the results

I am new here and I would greatly appreciate some help with a problem that I have been struggling with all day.

Consider these 2 tables:

         albums (pkey: id)    years (pkey: album_id, year)

         id   genre_id       album_id  year
         ---  --------       --------  ---- 
         450      1           450      2000
         451      1           450      2001
         452      1           450      2002
         453      2           451      2005
         454      3           451      2012
                              452      1998

It may seem strange, but please assume that a single album can be associated with multiple years.

I want to select all albums in genre 1, sorted by the latest year of each of those albums. (Assume is is possible to sort by year).

So, I want this result:

 id    year
-----  ----
 451   2012
 450   2002
 452   1998

This is where I am at...

Obviously, I need to join the tables before ordering the results:

SELECT      id, year
FROM        albums INNER JOIN years ON albums.id = years.album_id
WHERE       genre_id = 1
ORDER BY    year;

This selects every year record for each album in genre 1, instead of only the latest year record for each album in genre 1.

Somehow, I need to incorporate a sub query, like this, to limit the years joined to the album table, to only the latest year for that album, and then sort again:

SELECT year FROM years WHERE album_id = ??? ORDER BY year DESC LIMIT 1;

Upvotes: 1

Views: 2498

Answers (3)

Walter Mitty
Walter Mitty

Reputation: 18940

SELECT   album_id, max(year) latest
FROM albums INNER JOIN years ON id = album_id
WHERE genre_id = 1
GROUP BY album_id
ORDER BY latest DESC

Upvotes: 1

Andrew Logvinov
Andrew Logvinov

Reputation: 21851

That should be quite simple:

SELECT album_id, MAX(year) 
  FROM years
 GROUP BY album_id
 ORDER BY MAX(year) DESC;

Aggregate function max() allows you to select the latest year. Since you're using aggregate function, you also need a group by clause.

If you only need albums of a certain genre, just add join statement.

Upvotes: 1

kgu87
kgu87

Reputation: 2057

What you probably want is 

select
a.id,
( 
   select max(b.year) from years b where a.id = b.album_id
) as year
from 
albums a
where genre_id = 1
order by year

Upvotes: 0

Related Questions