Select first N items per relationship, one row per item

Question

Suppose I have the following schema:

artists:

    +------------+------------------+------+-----+---------+----------------+
    | Field      | Type             | Null | Key | Default | Extra                  |
    +------------+------------------+------+-----+---------+----------------+
    | id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |                   |
    | name       | varchar(255)     | YES  | UNI | NULL    |                |
    +------------+------------------+------+-----+---------+----------------+

events:

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra              |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment     |
| date       | timestamp        | YES  |     | NULL    |                |
| artist_id  | int(11)          | YES  |     | NULL    |                |
| venue_id   | int(11)          | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

assets:

+---------------------+------------------+------+-----+---------+----------------+
| Field               | Type             | Null | Key | Default | Extra          |
+---------------------+------------------+------+-----+---------+----------------+
| id                  | int(10) unsigned | NO   | PRI | NULL    | a
| event_id            | int(11)          | YES  |     | NULL    |                 
| source_asset_title  | varchar(255)     | YES  |     | NULL    |                |
| source_created_time | timestamp        | YES  |     | NULL    |                |

And I wanted a result set of 4 assets for each event for a given artist.id, sorted by event date such as:

+----------+----------+------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+
| event_id | asset_id | source_asset_title                                                                                                     | event_date          | date                |
+----------+----------+------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+
|        1 |     2089 | aba                                                                                                                       | 2015-12-03 07:00:00 | 2015-12-03 07:00:00 |
|        1 |     2101 | abb                                                                                                                        | 2015-12-03 07:00:00  | 2011-04-07 15:30:00 |
|        1 |     2102 | abc                                                                                                                    | 2015-12-03 07:00:00  | 2011-05-22 16:00:00 |
|        1 |     2107 | abd                                                                                                                     | 2015-12-03 07:00:00  | 2011-06-11 15:00:00 |
|        2 |     2109 | abe                                                                                                                     | 2011-07-18 15:00:00  | 2011-07-18 15:00:00 |
|        2 |     2113 | abf                                                                                                                    | 2011-07-18 15:00:00  | 2011-07-24 15:30:00 |
|        2 |     2115 | abg                                                                                                                    | 2011-07-18 15:00:00  | 2011-08-25 16:00:00 |
|        2 |     2123 | abh                                                                                                                    | 2011-07-18 15:00:00  | 2011-08-28 16:00:00 |
|        3 |     2126 | abi                                                                                                                    | 2011-09-01 16:00:00  | 2011-09-01 16:00:00 |
|        3 |     2129 | abj                                                                                                                    | 2011-09-01 16:00:00  | 2011-09-10 16:00:00 |
|        3 |     2135 | abk                                                                                                                    | 2011-09-01 16:00:00  | 2011-10-14 16:00:00 |
|        3 |     2147 | abl                                                                                                                    | 2011-09-01 16:00:00  | 2011-10-22 16:00:00 |

How could I achieve this without one subquery per event?

I believe that the schema and resultset here makes this different enough from other questions on StackExchange that a new question is appropriate.

Can you provide a dump or sqlfiddle, so we have some data to play around with? — Philipp, Jul 21 '16 at 18:26
You could use GROUP_CONCAT and http://stackoverflow.com/a/23608554/308851 — chx, Jul 21 '16 at 18:32
@chx Thanks! I believe that GROUP_CONCAT would place all of the values of the asset table into a single result row as a string, which doesn't match the output I'm hoping to get. (The end goal is to return json describing the assets). — ezra-g, Jul 21 '16 at 18:34
It seems like a potential alternate approach would be to do assemble a JSON object explicitly containing each of the fields from the assets table as part of the resultset using GROUP_CONCAT and CONCAT, similar to @chx's suggestion: http://stackoverflow.com/questions/12511933/how-create-json-format-with-group-concat-mysql — ezra-g, Jul 21 '16 at 18:44
Of course, then you run the risk of needing to increase the GROUP_CONCAT length limit: http://stackoverflow.com/questions/2567000/mysql-and-group-concat-maximum-length. — ezra-g, Jul 21 '16 at 18:54

score 0 · Answer 1 · answered Jul 21 '16 at 18:10

0

Here is the SQL for artist.id=1, ordered from newer to older by events.date

SELECT
  events.id as event_id,
  assets.id as asset_id,
  source_asset_title
FROM assets
INNER JOIN events ON events.id = assets.event_id
INNER JOIN artists ON artists.id = events.artist_id
WHERE artists.id = 1
ORDER BY events.date DESC

answered Jul 21 '16 at 18:10

Dmitry

729
7
19

Thanks, but this doesn't take into account the limit "4 assets for each event " which I see as the challenging part of the question. This query will show one row for every asset. – ezra-g Jul 21 '16 at 18:15
But wait for other answers – Dmitry Jul 21 '16 at 18:20
1

I concur with @ColdShine - Joins don't really work in a way that'll let you limit the results. You can use WHERE conditions in the ON statement of the join, but you can't sort and limit like that. – Cthos Jul 21 '16 at 18:42

score 0 · Accepted Answer · answered Jul 21 '16 at 18:59

No, it is not possible to achieve the specified result set without subqueries. As an alternative, consider GROUP_CONCAT, though you may have to increase the size of MySQL's group_concat_max_len to include all of the columns you wish to describe. This may or may not be a good idea.

Select first N items per relationship, one row per item

2 Answers2