Getting the "latest" related record from a SQL DB

[Mark Fowler]

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

Neither seem particularly tidy to me, so am I missing something
> completely obvious?
>

CREATE TEMP TABLE artist (
id INTEGER,
name TEXT
);

CREATE TEMP TABLE album (
id INTEGER,
artist INTEGER,
name TEXT,
release_date TIMESTAMP
);

INSERT INTO artist (id, name) VALUES (1, 'Weird Bob Olkavich');
INSERT INTO artist (id, name) VALUES (2, 'The Worms');

INSERT INTO album (id, artist, name, release_date) VALUES (1, 2,'Hard Days
Fight', '2001-01-01');
INSERT INTO album (id, artist, name, release_date) VALUES (2, 2,'Blue
Uboat','2001-01-02');
INSERT INTO album (id, artist, name, release_date) VALUES (3, 2,'Nickleback
Fighter','2001-01-03');

  SELECT *
    FROM album, artist
   WHERE album.artist = artist.id
ORDER BY release_date DESC
 LIMIT 1

 id | artist |        name        |    release_date     | id |   name

----+--------+--------------------+---------------------+----+-----------
  3 |      2 | Nickleback Fighter | 2001-01-03 00:00:00 |  2 | The Worms

Mark.