Getting the "latest" related record from a SQL DB

[Daniel Łukasiak]

Hi Andrew

Storing that data in a separate table might make sense if it will be accessed often.
However I would suggest a subselect if the above is not the case, technically these are two queries yes, but it’s up to the database to optimize them.

test=# select * from artist;
 id |  name
----+--------
  1 | Prince
  2 | Björk
(2 rows)

test=# select * from album;
 id | artist_id |          name
----+-----------+------------------------
  1 |         1 | 20Ten
  2 |         1 | Art Official Age
  3 |         1 | PLECTRUMELECTRUM
  4 |         2 | Drawing Restraint 9
  5 |         2 | Anna and the Moods
  6 |         2 | Björk: Biophilia Live
(6 rows)

test=# select artist.name, album.name from album join artist on (artist.id = album.artist_id) where album.id = (select max(id) from album where album.artist_id = artist.id);
  name  |          name
--------+------------------------
 Prince | PLECTRUMELECTRUM
 Björk | Björk: Biophilia Live
(2 rows)

Daniel

On 9 Oct 2014, at 13:28, Andrew Beverley <andy at andybev.com> wrote:

> Hi guys,
> 
> I'm after some best-practice advice regarding SQL database design.
> 
> I have a table (say "artist", couldn't resist...) that has a one-to-many
> relationship to another table (say "album"). The album table has a field
> which references the artist table's ID. So one artist can have many
> albums.
> 
> So, if I want to know all of an artist's albums, that's easy.
> 
> But what if I want to fetch an artist's details and his latest album? I
> can select the artist from the artists table and then join the albums
> table. But to get the latest album I'd have to use a max function (say
> on the album's date), with which it isn't possible to get the related
> fields in the same row.
> 
> I see 2 ways of solving this:
> 
> - Run multiple queries to get the relevant album's ID (if even possible)
> and then retrieve its row in entirety.
> 
> - Have a reference from the artist table back to the album table,
> specifying which is the latest album, which I update each time the
> albums table is updated.
> 
> Neither seem particularly tidy to me, so am I missing something
> completely obvious?
> 
> Thanks,
> 
> Andy
> 
>