Question 162497 in Mysql Question posted in
The official MySQL documentation can be found here.

Can MySQL order & group rows together?

Having problem with complex order.

Here is structure and data:

CREATE TABLE `mail_test` (
  `id` int(10) UNSIGNED NOT NULL,
  `account_id` int(10) UNSIGNED NOT NULL,
  `score` float UNSIGNED NOT NULL,
  `from` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `mail_test` (`id`, `account_id`, `score`, `from`) VALUES
(1, 1, 0, '[email protected]'),
(2, 2, 0, '[email protected]'),
(3, 3, 3, '[email protected]'),
(4, 5, 4, '[email protected]'),
(5, 3, 1, '[email protected]'),
(6, 9, 0.5, '[email protected]'),
(7, 9, 3, '[email protected]'),
(8, 8, 2, '[email protected]');

Desired output:

(4, 5, 4, '[email protected]'),
(7, 9, 3, '[email protected]'),
(3, 3, 3, '[email protected]'),
(5, 3, 1, '[email protected]'),
(8, 8, 2, '[email protected]');
(6, 9, 0.5, '[email protected]'),
(1, 1, 0, '[email protected]'),
(2, 2, 0, '[email protected]'),

The order logic: first is row with greatest score (parent row), then rows (child rows) with same account_id and from with parent row. If no child rows – again next row with great score. So order by score DESC. But rows grouped by same account_id and from.

mysql version 5.7

Tags:

3

Answers


  1. 0

    This can be done using row_number() and max() :

    with cte as (
  select *,
           max(score) over (partition by account_id, `from`) as max_score,
           row_number() over (partition by account_id, `from` order by score, id) as rn
  from mail_test
)
select id, account_id, score, `from`
from cte
order by max_score desc, rn desc

    This is a working solution on mysql 5.7 :

    select mt.*
from `mail_test` mt
inner join (
  select account_id, `from`, max(score) as max_score
  from `mail_test`
  group by account_id, `from`
) as s on s.account_id = mt.account_id and s.`from` = mt.`from`
order by max_score desc, id

    Result :

    id  account_id  score   from
4   5           4       [email protected]
3   3           3       [email protected]
5   3           1       [email protected]
7   9           3       [email protected]
8   8           2       [email protected]
6   9           0.5     [email protected]
1   1           0       [email protected]
2   2           0       [email protected]

    Demo here

    Login or Signup to reply.
  2. 0 
    SELECT *
FROM mail_test
ORDER BY MAX(score) OVER (PARTITION BY account_id, `from`) DESC,
         account_id, `from`,
         ROW_NUMBER() OVER (PARTITION BY account_id, `from` ORDER BY score DESC)
    id account_id score from
    4 5 4 [email protected]
    3 3 3 [email protected]
    5 3 1 [email protected]
    7 9 3 [email protected]
    8 8 2 [email protected]
    6 9 0.5 [email protected]
    1 1 0 [email protected]
    2 2 0 [email protected]

    fiddle

    Login or Signup to reply.
  3. 0

    This version works without partitions:

    SELECT m.`id`, m.`account_id`, m.`score`, m.`from`
FROM mail_test m
LEFT JOIN mail_test parent_m
  ON m.account_id = parent_m.account_id
  AND m.from = parent_m.from
  AND m.score < parent_m.score
GROUP BY m.id, m.account_id, m.score, m.from
ORDER BY IFNULL(MAX(parent_m.score), m.`score`) DESC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search