ORDER/LIMIT on specific column in JOIN

Question

I have the following sql statement:

SELECT e.comment_id AS parentcomment,
m.comment_id AS child_id
FROM comments e
INNER JOIN comments m
ON e.comment_id=m.parent_id
WHERE e.parent='1' AND e.parent_id='$parentid' AND m.parent='0';

Sample output:

+---------------+----------+
| parentcomment | child_id |
+---------------+----------+
|             1 |        3 |
|             1 |        4 |
|             1 |        7 |
|             5 |        8 |
|             1 |        9 |
|             1 |       10 |
|             1 |       11 |
|             1 |       12 |
|             1 |       13 |
|             1 |       14 |
|             1 |       15 |
|             1 |       16 |
|             1 |       17 |
|             1 |       18 |
|             1 |       19 |
|             1 |       20 |
|             1 |       21 |
|             1 |       22 |
|             1 |       23 |
|             1 |       24 |
|             1 |       25 |
|            26 |       32 |
|            26 |       33 |
|            27 |       34 |
|            27 |       35 |
|            28 |       36 |
|            29 |       37 |
|            30 |       38 |
|            31 |       39 |
|            26 |       40 |
+---------------+----------+
30 rows in set

What I want to do - I only want to show 15 child_id values for each parentcomment value. In addition to that, I only want 30 parentcomment values to be retrieved.

MOREOVER, I want to ORDER the parentcomment values by a certain index (let's say id) and pick out only the top 30, and then pick out the top 15 out of the ordered child_id values (also ordered by id).

Hope this makes sense. How do I do those things?

Thanks in advance.

can you provide sample data and desired output? like maybe instead of 15 child_id, try 3 or something smaller and instead of 30 parentcomment try something like 2 parentcomment and provide sample data. Then once you have that working changing the numbers to 15 child_id and 30 parentcomment should be easy. I am not sure if you mean 30 rows or 30 unique parentcomment which is possibly (30x15) rows — Tin Tran, Dec 24 '13 at 23:01

score 1 · Accepted Answer · answered Dec 24 '13 at 23:45

you should be able to work with this (sqlFiddle) here i am only grabbing 3 (instead of 15) child_id and 6 (instead of 30) parentcomment

and in my example, the $parentid is 1. The inner most query is your original query just added the ORDER BY parentcomment,child_id

SELECT parentcomment,child_id,parentRank,childRank
FROM
    (SELECT parentcomment,
           child_id,
           IF (@prevparent <> parentcomment, @parentRank:=@parentRank+1, @parentRank) as parentRank,
           IF (@prevparent <> parentcomment, @childRank:=1, @childRank:=@childRank+1) as childRank,
           @prevparent := parentcomment
     FROM
       (SELECT e.comment_id AS parentcomment,
               m.comment_id AS child_id
        FROM comments e
        INNER JOIN comments m
        ON e.comment_id=m.parent_id
        WHERE e.parent='1' AND e.parent_id='1' AND m.parent='0'

          ORDER BY parentcomment,child_id
       )T1,
       (SELECT @prevparent:=0,@parentRank:=0,@childRank:=0)rank
     )T2
WHERE parentRank BETWEEN 1 AND 6
AND   childRank BETWEEN 1 AND 3

ORDER/LIMIT on specific column in JOIN

1 Answers1