SQL sorting by number and stay grouped

Question

Thank you for the time you'll spend to read and possibly answered to my question.

I've a little problem of sorting I show you :

My SQL query is that :

SELECT p.advisor_create, COUNT(p.id) AS Nb, IF(p.cancel_advisor IS 
NULL, "no", "yes") AS refund
FROM payment p
WHERE p.type = 'CESSATION'
AND p.date BETWEEN '2017-01-01 00:00:00' AND '2017-11-31 23:59:59'
GROUP BY p.advisor_create, refund
ORDER BY p.advisor_create, Nb DESC

The data I get from this is like that :

+-------+---+------+
|advisor| NB|refund|
+-------+---+------+
| 170432| 50|    no|
| 170432|  4|   yes|
| 175222| 30|    no|
| 175222|  3|   yes|
| 182985|304|    no|
| 182985| 19|   yes|
| 362912|360|    no|
| 362912| 13|   yes|
+-------+---+------+

And I would to sort like this :

+-------+---+------+
|advisor| NB|refund|
+-------+---+------+
| 362912|360|    no|
| 362912| 13|   yes|
| 182985|304|    no|
| 182985| 19|   yes|
| 170432| 50|    no|
| 170432|  4|   yes|
| 175222| 30|    no|
| 175222|  3|   yes|
+-------+---+------+

Sort by MAX(NB) of same "advisor" and group the two line of an advisor together.

Thank you again for your help.

The solution : Thank to @gordon-linoff for this

SELECT p.advisor_create, COUNT(p.id) AS Nb,
   IF(p.cancel_advisor IS NULL, 'no', 'yes') AS refund
FROM payment p
WHERE p.type = 'CESSATION' AND
  p.date >= '2017-01-01' AND 
  p.date < '2017-12-01'
GROUP BY p.advisor_create, refund
ORDER BY (SELECT COUNT(*)
      FROM payment p2
      WHERE p2.advisor_create = p.advisor_create AND
            p2.type = 'CESSATION' AND
            p2.date >= '2017-01-01' AND 
            p2.date < '2017-12-01' AND
            p2.cancel_advisor IS NULL
     ) DESC,
     p.advisor_create, Nb DESC

Answer 1

You can use a subquery in the order by. This allows you to do:

SELECT p.advisor_create, COUNT(p.id) AS Nb,
       IF(p.cancel_advisor IS NULL, 'no', 'yes') AS refund
FROM payment p
WHERE p.type = 'CESSATION' AND
      p.date >= '2017-01-01' AND 
      p.date < '2017-12-01'
GROUP BY p.advisor_create, refund
ORDER BY (SELECT COUNT(*)
          FROM payment p2
          WHERE p2.advisor_create = p.advisor_create AND
                p2.type = 'CESSATION' AND
                p2.date >= '2017-01-01' AND 
                p2.date < '2017-12-01' AND
                p2.cancel_advisor IS NULL
         ),
         p.advisor_create, Nb DESC;

This assumes you want to sort by the "no" value. If you want to sort by the total, just remove that condition from the subquery.

Also notice that I simplified the date comparisons. There is no reason to go to seconds -- in fact, between is not recommended for date/time comparisons, because of the confusion that arises with the time component.