MySQL query limit per join

Question

I tried asking question before, but it's hard to ask in specific without right terminology I am not quite familiar with. So here is an example

Take this query for example:

(
SELECT *
FROM comments
WHERE depth = 0
ORDER BY id DESC
LIMIT 2
)
UNION ALL
(
  SELECT c.*
    FROM comments c JOIN 
    (
      SELECT id
      FROM comments
      WHERE depth = 0
      ORDER BY id DESC
      LIMIT 2
    ) p ON c.parent_id = p.id
    LIMIT 5
)


id  parent_id   depth   title
1   0           0       Title 1
2   0           0       Title 2
3   1           1       Title 3
4   1           1       Title 4
5   1           1       Title 5
6   1           1       Title 6
7   1           1       Title 7

I get two depth 0 rows and in join I get 5 child elements of those two returned queries as well. What I would like to get is to get 5 child elements of each of those two queries, total of 10 rows (of depth 1). For example:

id  parent_id   depth   title
1   0           0       Title 1
2   0           0       Title 2
3   1           1       Title 3
4   1           1       Title 4
5   1           1       Title 5
6   1           1       Title 6
7   1           1       Title 7
8   2           1       Title 8
9   2           1       Title 9
10  2           1       Title 10
11  2           1       Title 11
12  2           1       Title 12

Is that even possible with adjacency list and a requirement to return everything as union (flat)?

edit: Thanks to Bill Karwin's answer, I got it working now. I wonder still if there is a shorter way to write this. I have 6 (0-5) depth levels, so my query is rather long (and probably not optimal). Here is what it looks like for three levels (you can imagine what the full one looks like).

-- DEPTH LEVEL 0
(
  SELECT * FROM (
    SELECT *, 1 as _rn, @parent:=0
    FROM comments
    WHERE depth = 0
    ORDER BY id DESC
    LIMIT 2
  ) as D0
)
union all

-- DEPTH LEVEL 1
(
SELECT *
FROM (
  SELECT c.*, @row:=IF(@parent=c.comment_id,@row+1,1) AS _rn, @parent:=c.comment_id
    FROM (SELECT @parent:=null) AS _init 
    STRAIGHT_JOIN comments c 
    INNER JOIN 
    (
      SELECT id
      FROM comments
      WHERE depth = 0
      ORDER BY id DESC
      LIMIT 2
    ) p ON c.comment_id = p.id
  ) AS _ranked
WHERE _ranked._rn <= 5
)
union all

-- DEPTH LEVEL 2
(
SELECT *
FROM (
  SELECT c.*, @row:=IF(@parent=c.comment_id,@row+1,1) AS _rn, @parent:=c.comment_id
    FROM (SELECT @parent:=null) AS _init 
    STRAIGHT_JOIN comments c 
    INNER JOIN 
    (

      (
      SELECT *
      FROM (
        SELECT c.*, @row:=IF(@parent=c.comment_id,@row+1,1) AS _rn, @parent:=c.comment_id
          FROM (SELECT @parent:=null) AS _init 
          STRAIGHT_JOIN comments c 
          INNER JOIN 
          (
            SELECT id
            FROM comments
            WHERE depth = 0
            ORDER BY id DESC
            LIMIT 2
          ) p ON c.comment_id = p.id
        ) AS _ranked
      WHERE _ranked._rn <= 2
      )

    ) p ON c.comment_id = p.id
  ) AS _ranked
WHERE _ranked._rn <= 2
)

MySQL doesn't have built-in N-per-group support, but if you search SO for other questions with that tag you'll find various workarounds. — Barmar, Oct 01 '13 at 16:39

score 1 · Accepted Answer · answered Oct 01 '13 at 16:41

1

You can't do this with LIMIT, because LIMIT is applied after the result set is completely finished, after all joining, grouping, sorting, etc.

You're using a variation of the greatest-n-per-group type of query. It's tricky to do this in MySQL because MySQL doesn't support the ROW_NUMBER() window function supported by many other SQL databases.

Here's a workaround for MySQL, in which user-defined variables can take the place of partitioned row numbers:

SELECT *
FROM (
  SELECT c.*, @row:=IF(@parent=c.parent_id,@row+1,1) AS _rn, @parent:=c.parent_id
    FROM (SELECT @parent:=null) AS _init 
    STRAIGHT_JOIN comments c 
    INNER JOIN 
    (
      SELECT id
      FROM comments
      WHERE depth = 0
      ORDER BY id DESC
      LIMIT 2
    ) p ON c.parent_id = p.id
  ) AS _ranked
WHERE _ranked._rn <= 5

answered Oct 01 '13 at 16:41

Bill Karwin

538,548
86
673
828

Excellent, thanks a lot! Is there a, well, simpler way to extend this to work for 6 (0-5) depths though? MySQL sure leaves a lot to be desired. – Keyframe Oct 01 '13 at 16:55
I can't tell what you have in mind. You should edit your question to show an example of the output you want, or else ask a new question. – Bill Karwin Oct 01 '13 at 17:45
Again, thanks Bill. I was wondering if I could shorten the query, I've added an example. – Keyframe Oct 01 '13 at 18:35
You might want to consider using another way to organize hierarchical data besides Adjacency List. See for example [What is the most efficient/elegant way to parse a flat table into a tree?](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/192462#192462). – Bill Karwin Oct 01 '13 at 19:04

MySQL query limit per join

1 Answers1