All geek questions in one place

How can I order selected rows as an alternative

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
FROM 
(
  SELECT rid, rootid, which, COUNT(*) AS cnt
  FROM
  (
    SELECT rid, rootid, 'vote' which FROM p_likes
    UNION ALL 
    SELECT rid, rootid, 'comment' which FROM p_comments
    UNION ALL 
    SELECT rid, rootid, 'friend' which FROM relations
  ) 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.cnt desc;

And here is the query output above: ( //means the value of this entry is not related to this question.)

+---------+-----------+----------+------------+-------------+-------+
| user_id | user_name | liker_id | liker_name | which_table | total |
+---------+-----------+----------+------------+-------------+-------+
| //      | //        | //       | //         | vote        | 7     |
| //      | //        | //       | //         | vote        | 5     |
| //      | //        | //       | //         | vote        | 3     |
| //      | //        | //       | //         | comment     | 3     |
| //      | //        | //       | //         | vote        | 2     |
| //      | //        | //       | //         | comment     | 2     |
| //      | //        | //       | //         | comment     | 2     |
| //      | //        | //       | //         | vote        | 1     |
| //      | //        | //       | //         | vote        | 1     |
| //      | //        | //       | //         | vote        | 1     |
| //      | //        | //       | //         | comment     | 1     |
| //      | //        | //       | //         | friend      | 1     |
+---------+-----------+----------+------------+-------------+-------+

All I'm trying to do is sort the strings alternatively. As you can see, I am currently ordering results based on a column total. Although I need to sort them both based totaland which_table. Something like this: (expected result)

+---------+-----------+----------+------------+-------------+-------+
| user_id | user_name | liker_id | liker_name | which_table | total |
+---------+-----------+----------+------------+-------------+-------+
| //      | //        | //       | //         | vote        | 7     |
| //      | //        | //       | //         | comment     | 3     |
| //      | //        | //       | //         | friend      | 1     |
| //      | //        | //       | //         | vote        | 5     |
| //      | //        | //       | //         | comment     | 2     |
| //      | //        | //       | //         | vote        | 3     |
| //      | //        | //       | //         | comment     | 2     |
| //      | //        | //       | //         | vote        | 2     |
| //      | //        | //       | //         | comment     | 1     |
| //      | //        | //       | //         | vote        | 1     |
| //      | //        | //       | //         | vote        | 1     |
| //      | //        | //       | //         | vote        | 1     |
+---------+-----------+----------+------------+-------------+-------+

How can i do this?

+4
source share
3 answers

. ROW_NUMBER() OVER(PARTITION BY ..) / 'which' . (, , script, )

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, which, COUNT(*) AS cnt
        ,ROW_NUMBER() OVER(PARTITION  BY which ORDER BY rid) AS new_order
  FROM
  (
    SELECT rid, rootid, 'vote' which FROM p_likes
    UNION ALL 
    SELECT rid, rootid, 'comment' which FROM p_comments
    UNION ALL 
    SELECT rid, rootid, 'friend' which FROM relations
  ) 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 new_order,x.cnt desc;
0

, . , - ( live) , id

MariaDB [sandbox]> select * from onetime_contest;
+------+-----------+------------+
| id   | status    | valid_till |
+------+-----------+------------+
|    1 | live      | 2017-01-01 |
|    2 | waiting   | 2017-01-01 |
|    3 | completed | 2017-01-01 |
|    4 | Waiting   | 2017-01-01 |
|    5 | live      | 2017-06-01 |
|    6 | waiting   | 2017-06-01 |
|    7 | completed | 2017-06-01 |
|    8 | Waiting   | 2017-06-01 |
|    1 | Live      | 2017-01-01 |
|    2 | Live      | 2017-01-01 |
|    3 | Live      | 2017-01-01 |
|    3 | Waitng    | 2017-01-01 |
|    4 | Live      | 2017-01-01 |
|    6 | Live      | 2017-01-01 |
|    7 | Live      | 2017-01-01 |
|    8 | Live      | 2017-01-01 |
|    1 | Waiting   | 2017-01-01 |
|    2 | Waiting   | 2017-01-01 |
|    2 | Live      | 2017-01-01 |
|    2 | Waiting   | 2017-01-01 |
|    2 | Live      | 2017-01-01 |
|    2 | Live      | 2017-01-01 |
+------+-----------+------------+
22 rows in set (0.00 sec)

MariaDB [sandbox]>
MariaDB [sandbox]>
MariaDB [sandbox]> select * from
    -> (
    -> select id, status ,count(*) as t1 ,count(*) as t2 from onetime_contest o1 where status = 'Live' group by id,status
    -> union
    -> select id, status ,count(*) as t1 ,(select count(*) from onetime_contest o2 where status = 'Live' and o2.id = o1.id  group by o2.id,o2.status)
    ->   from onetime_contest o1 where status = 'Waiting' group by id,status
    -> union
    -> select id, status ,count(*) as t1 ,(select count(*) from onetime_contest o2 where status = 'Live' and o2.id = o1.id  group by o2.id,o2.status)
    ->  from onetime_contest o1 where status = 'Completed' group by id,status
    -> ) s
    -> order by s.t2 desc,s.id,
    -> case when s.status = 'Live' then 1
    ->  when s.status = 'Waiting' then 2
    -> else 3 end;
+------+-----------+----+------+
| id   | status    | t1 | t2   |
+------+-----------+----+------+
|    2 | Live      |  4 |    4 |
|    2 | waiting   |  3 |    4 |
|    1 | live      |  2 |    2 |
|    1 | Waiting   |  1 |    2 |
|    3 | Live      |  1 |    1 |
|    3 | completed |  1 |    1 |
|    4 | Live      |  1 |    1 |
|    4 | Waiting   |  1 |    1 |
|    5 | live      |  1 |    1 |
|    6 | Live      |  1 |    1 |
|    6 | waiting   |  1 |    1 |
|    7 | Live      |  1 |    1 |
|    7 | completed |  1 |    1 |
|    8 | Live      |  1 |    1 |
|    8 | Waiting   |  1 |    1 |
+------+-----------+----+------+
15 rows in set (0.00 sec)
0

, :

SELECT 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, which, COUNT(*) AS cnt FROM ( SELECT rid, rootid, 'vote' which FROM p_likes UNION ALL SELECT rid, rootid, 'comment' which FROM p_comments UNION ALL SELECT rid, rootid, 'friend' which FROM relations ) 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 1,2,3,4,5,6;

. , total which_table, : SELECT ( ) 5,6

-1

Source: https://habr.com/ru/post/1668491/

More articles:


All Articles