How can I get the top ten?

Question

Here is my request:

SELECT
  pr1.id AS user_id,
  pr1.title AS user_name,
  pr2.id AS liker_id,
  pr2.title AS liker_name,
  x.which AS which_table,
  x.cnt AS total,
  x.rank AS rk
FROM 
(
  SELECT rid, rootid, which, COUNT(*) AS cnt, rank
  FROM
  (
    SELECT @rank1 := @rank1 + 3 rank, rid, rootid, 'vote' which
    FROM p_likes, (select @rank1 := -2) q
    UNION ALL 
    SELECT @rank2 := @rank2 + 3 rank, rid, rootid, 'comment' which
    FROM p_comments, (select @rank2 := -1) q
    UNION ALL 
    SELECT @rank3 := @rank3 + 3 rank, rid, rootid, 'friend' which
    FROM relations, (select @rank3 := 0) q
  ) y
  WHERE y.rootid = 1246 AND y.rootid <> y.rid
  GROUP BY y.rid, y.rootid, y.which
) x
INNER JOIN pagesroot pr1 on x.rootid = pr1.id
INNER JOIN pagesroot pr2 on x.rid = pr2.id
ORDER BY x.rank desc, x.cnt desc, x.which
LIMIT 30;

My question is about ordering. I want each table to be 10 rows (if they exist). There are three tables ( p_likes, p_comments, relations). Thus, the result should be a maximum of 30 lines.

But what is my question: I need to place an order alternatively. I mean, the first line should be from p_likes, the second from p_comments, the third from relations, the fourth from p_likes, etc.

I also want to sort them based on the column first cnt. I want to say that I want a ten of each table.

How can i do this?

Answer 1

, , , : num

SELECT @rank1 := @rank1 + 3 rank, rid, rootid, 'vote',1 as num which
FROM p_likes, (select @rank1 := -2) q group by y.rid, y.rootid limit 1,10
UNION ALL 
SELECT @rank2 := @rank2 + 3 rank, rid, rootid, 'comment',2 as num which
FROM p_comments, (select @rank2 := -1) q y.rid, y.rootid limit 1,10
UNION ALL 
SELECT @rank3 := @rank3 + 3 rank, rid, rootid, 'friend',3 as num which
FROM relations, (select @rank3 := 0) q  y.rid, y.rootid limit 1,10

Answer 2

rid, rootid, which. rid, rootid, which, COUNT(*) AS cnt, rank. , rid, rootid, which rid, rootid, which. rid, rootid, which , ? rid, rootid, which 3, 6, 9, , , ? ( MIN MAX), . , .

, 10 ( rid rootid 1246, ). rootid = 1246 and rootid <> rid . LIMIT, .

SELECT
  pr1.id AS user_id,
  pr1.title AS user_name,
  pr2.id AS liker_id,
  pr2.title AS liker_name,
  x.which AS which_table,
  x.cnt AS total
FROM 
(
  (
    SELECT rid, rootid, 'vote' AS which, COUNT(*) AS cnt
    FROM p_likes
    WHERE rootid = 1246 and rootid <> rid
    ORDER BY COUNT(*) DESC
    LIMIT 10
  )
  union all 
  (
    SELECT rid, rootid, 'comment' AS which, COUNT(*) AS cnt
    FROM p_comments
    WHERE rootid = 1246 and rootid <> rid
    ORDER BY COUNT(*) DESC
    LIMIT 10
  )
  union all 
  (
    SELECT rid, rootid, 'friend' AS which, COUNT(*) AS cnt
    FROM relations
    WHERE rootid = 1246 and rootid <> rid
    ORDER BY COUNT(*) DESC
    LIMIT 10
  )
) x
INNER JOIN pagesroot pr1 ON x.rootid = pr1.id
INNER JOIN pagesroot pr2 ON x.rid = pr2.id
ORDER BY x.cnt DESC, x.which;