Mysql select most recent with date desc for each artist

Question

i have 3 tables:

PK: Primary Key
FK: Foreign Key

mp3s table:

+---------+--------------+----------------------+
| id (PK) | tarck_title  | date                 |
+---------+--------------+----------------------+
| 100     | shakira      |  2001-01-12 00:00:00 | 
| 101     | metallica    |  2002-01-12 00:00:00 | 
| 102     | james blunt  |  2003-01-12 00:00:00 | 
| 103     | shakira      |  2004-01-12 00:00:00 | 
| 104     | anathema     |  2005-01-12 00:00:00 | 
| 105     | nelson       |  2006-01-12 00:00:00 | 
| 106     | shakira      |  2007-01-12 00:00:00 | 
| 107     | bb king      |  2008-01-12 00:00:00 | 
| 108     | metallica    |  2009-01-12 00:00:00 | 
| 109     | nelson       |  2010-01-12 00:00:00 | 
| 110     | shakira      |  2011-01-12 00:00:00 | 
| 111     | bb king      |  2012-01-12 00:00:00 | 
+---------+--------------+----------------------+

artists table:

+---------+----------------+
| id (PK) | artist_name    |
+---------+----------------+
| 14      | shakira        |
| 221     | metallica      |
| 320     | james blunt    |
| 328     | shakira        |
| 1004    | anathema       |
| 1140    | nelson         |
| 1401    | bb king        |
+---------+----------------+

and tags table: PK(mp3_id, artist_id)

+-------------+----------------+
| mp3_id (FK) | artist_id (FK) |
+-------------+----------------+
| 100         | 14             |
| 101         | 221            |
| 102         | 320            |
| 103         | 14             |
| 104         | 1004           |
| 105         | 1140           |
| 106         | 14             |
| 107         | 1401           |
| 108         | 221            |
| 109         | 1140           |
| 110         | 14             |
| 111         | 1401           |
+---------+--------------------+

now, i need good query for this resaults. i want select 3 latest track from (shakira & bb king) order by date track. like this:

+---------+--------------+----------------------+
| id (PK) | tarck_title  | date                 |
+---------+--------------+----------------------+
| 110     | shakira      |  2011-01-12 00:00:00 | 
| 106     | shakira      |  2007-01-12 00:00:00 | 
| 103     | shakira      |  2004-01-12 00:00:00 | 
| 111     | bb king      |  2012-01-12 00:00:00 | 
| 107     | bb king      |  2008-01-12 00:00:00 | 
+---------+--------------+----------------------+

or select 3 latest track from (shakira & bb king & metallica) order by date track. like this:

+---------+--------------+----------------------+
| id (PK) | tarck_title  | date                 |
+---------+--------------+----------------------+
| 110     | shakira      |  2011-01-12 00:00:00 | 
| 106     | shakira      |  2007-01-12 00:00:00 | 
| 103     | shakira      |  2004-01-12 00:00:00 | 
| 111     | bb king      |  2012-01-12 00:00:00 | 
| 107     | bb king      |  2008-01-12 00:00:00 | 
| 108     | metallica    |  2009-01-12 00:00:00 | 
| 101     | metallica    |  2002-01-12 00:00:00 | 
+---------+--------------+----------------------+

EDIT:

this query is working but sort date desc not working:

SELECT `id`, `tarck_title`, `date`
        FROM `mp3s`
        WHERE `id` IN (
            SELECT x.`mp3_id`
            FROM `tags` x
            INNER JOIN `tags` y ON y.`artist_id` = x.`artist_id` AND y.`mp3_id` <= x.`mp3_id`
            INNER JOIN `mp3s` z ON z.`id` = x.`mp3_id`
            WHERE x.`artist_id` IN (SELECT `artist_id` FROM `tags` WHERE `mp3_id` = 103)
            GROUP BY x.`mp3_id` HAVING COUNT(*) <= 3
            ORDER BY z.`date` DESC, x.`artist_id` DESC, x.`mp3_id`)

Ok... So aren't you able to select? What query did you try? What result did it return? — Chetan, Jun 18 '17 at 10:00
@ChetanRanpariya i test 2, 3 query try. but order by date is my problem — Farzad, Jun 18 '17 at 10:10

OmG · Answer 1 · 2017-06-18T10:53:34.347

1

If I found the question, the query would be like the following:

  (SELECT m.* FROM (tags as t JOIN artists as a on t.artist_id = a.id) JOIN mp3s as m on m.id = t.mp3_id
  WHERE a.artist_name = 'Shakira'
  ORDER BY m.date DESC
  LIMIT 3)
  UNION
  (SELECT m.* FROM (tags as t JOIN artists as a on t.artist_id = a.id) JOIN mp3s as m on m.id = t.mp3_id
  WHERE a.artist_name = 'bb King'
  ORDER BY m.date DESC
  LIMIT 3);

Also, If you want a compressed format of the query, you can find it in this post.

edited Jun 18 '17 at 10:53

answered Jun 18 '17 at 10:41

OmG

18,337
10
57
90

your answer is true, but i want without `union`. because number of `a.artist_name` not fix. – Farzad Jun 18 '17 at 11:12
@grizzly Did you see the referenced post? – OmG Jun 18 '17 at 11:29
yes. [this post](https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) ??? – Farzad Jun 18 '17 at 11:33
@grizzly Ya. it's the exactly as same as your question. – OmG Jun 18 '17 at 11:38

Gordon Linoff · Answer 2 · 2017-06-18T13:10:21.733

1

In MySQL, you can do this with variables:

SELECT m.*
FROM (SELECT m.*,
             (@rn := if(@a = a.id, @rn + 1,
                        if(@a := a.id, 1, 1)
                       )
             ) as rn
      FROM tags t JOIN
           artists a 
           ON t.artist_id = a.id JOIN
           mp3s m 
           ON m.id = t.mp3_id CROSS JOIN
           (SELECT @a := -1, @rn := 0) params
      WHERE . . .
      ORDER BY a.id, m.date DESC
     ) m
WHERE rn <= 3;

In the inner WHERE, you can specify whatever artists or other conditions that you like.

edited Jun 18 '17 at 13:10

answered Jun 18 '17 at 11:51

Gordon Linoff

1,242,037
58
646
786

I've tested, but not sorted by date – Farzad Jun 18 '17 at 11:54
@grizzly . . . I don't understand your comment. You can sort the overall data however you want with an `order by` in the outermost query. Your question seems to be about choosing the top three for each artist based on the most recent date. Are you saying that doesn't work? – Gordon Linoff Jun 18 '17 at 12:51
`m.date desc` doesn't work. Song selection was based on the old date – Farzad Jun 18 '17 at 13:07
@grizzly . . . How are the dates stored, as dates? It is hard to see how this would not return the three most recent dates for each artist. Perhaps the wrong id was being used. I notice the right column is `a.id` for the `order by` and the `if()`. – Gordon Linoff Jun 18 '17 at 13:10

Mysql select most recent with date desc for each artist

2 Answers2