Getting the "latest" related record from a SQL DB

[Abigail]

On Fri, Oct 10, 2014 at 09:05:03AM +0100, James Laver wrote:
> 
> On 9 Oct 2014, at 22:36, Abigail <abigail at abigail.be> wrote:
> 
> > This assumes no artist releases two albums at the same time.
> 
> Not really, it’s just that in that case it will only return one, which is probably acceptable behaviour.

I would think my query would return all of them, lacking any clause
differentiating between albums with the same release date.

> 
> But that’s why the declarative nature of sql is brilliant, I can tell you exactly what it will do without running it.
> 

MySQL disagrees with your assessment:


mysql> CREATE TABLE Artist (id   INTEGER PRIMARY KEY AUTO_INCREMENT,
                            name VARCHAR(255) NOT NULL);
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE Album (id           INTEGER PRIMARY KEY AUTO_INCREMENT,
                           artist_id    INTEGER REFERENCES Artist,
                           name         VARCHAR(255) NOT NULL,
                           release_date DATE NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO Artist (name) VALUES ("John"), ("Paul"),
                                        ("Ringo"), ("George");
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO Album (artist_id, name, release_date)
                  VALUES (1, "Songs by John", "2001-01-01"),
                         (1, "More Songs by John", "2002-01-01"),
                         (1, "The best of John, Vol. 1", "2003-01-01"),
                         (1, "The best of John, Vol. 2", "2003-01-01"),
                         (2, "Songs by Paul", "2003-01-01"),
                         (2, "More Songs by Paul", "2004-01-01"),
                         (2, "The best of Paul, Vol. 1", "2005-01-01"),
                         (2, "The best of Paul, Vol. 2", "2006-01-01");
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT  Artist.*, Album.name
         FROM  Artist, Album
        WHERE  Artist.id = Album.artist_id
          AND  Artist.id = 1
          AND  Album.release_date = (SELECT  MAX(Album.release_date)          
                                       FROM  Artist, Album
                                      WHERE  Artist.id = Album.artist_id 
                                        AND  Artist.id = 1);
+----+------+--------------------------+
| id | name | name                     |
+----+------+--------------------------+
|  1 | John | The best of John, Vol. 1 |
|  1 | John | The best of John, Vol. 2 |
+----+------+--------------------------+
2 rows in set (0.01 sec)

mysql> SELECT  Artist.*, Album.name
         FROM  Artist, Album
        WHERE  Artist.id = Album.artist_id
          AND  Artist.id = 2
          AND  Album.release_date = (SELECT  MAX(Album.release_date)          
                                       FROM  Artist, Album
                                      WHERE  Artist.id = Album.artist_id 
                                        AND  Artist.id = 2);
+----+------+--------------------------+
| id | name | name                     |
+----+------+--------------------------+
|  2 | Paul | The best of Paul, Vol. 2 |
+----+------+--------------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE Artist; DROP TABLE Album;
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>


Abigail