CODEWITHSUNDEEP
mysqlsql
stack
stack

Reputation: 10228

How can I get top ten alternatively?

Here is my query:

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 the ordering. I want to get 10 rows (if exist) form each table. There is 3 tables (p_likes, p_comments, relations). So the result should be 30 rows in maximum.

But what's my question: I need to make a order alternatively. I mean first row should be from p_likes, second one should be from p_comments, third one should be from relations, fourth one should be from p_likes and so on ..

I also want to sort them based on the cnt column first. I mean, I want top ten of each table.

How can I do that?

Upvotes: 0

Views: 70

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94939

You are grouping by rid, rootid, which. You select rid, rootid, which, COUNT(*) AS cnt, rank. So you get the rid, the rootid, the which and the count of records per rid, rootid, which. But which of all the ranks per rid, rootid, which do you think you get? If there are ranks 3, 6, 9 for the rid, rootid, which, which do you think the DBMS will pick? You don't tell it (which you would be able to do with MIN or MAX), so the DBMS picks a rank arbitrarily. You see, your approach cannot work.

In the comments section you say you want the 10 records per table with the highest count (per rid for rootid 1246 obviously). So you must apply rootid = 1246 and rootid <> rid for each table and count directly. Then use LIMIT to only take up to ten records per table.

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;

Upvotes: 1

krishn Patel
krishn Patel

Reputation: 2599

you should add number as you want to get order and give first order by it like below as 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

Upvotes: 1

Related Questions