ORDER BY clause in CASE WHEN

Hi, I can’t order by this query, why?
I need order for rdt value UOT first value, MAL second value, PMC three value and RAS four value.
Thanks in advance

mysql> SELECT
	IFNULL(`RDT`, 'tot') AS RDT,
	IFNULL(`type`, 'tot') AS `type`,
	SUM(`number`) AS `number`,
	`total`,
	SUM(`perc`) AS `perc`,
	`thedate`
FROM
	(
		SELECT
			IFNULL(`RDT`, 'tot') AS RDT,
			`type`,
			`NUMBER`,
			`total`,
			`perc`,
			`THEDATE`
		FROM
			`dotable`
		GROUP BY
			`RDT`,
			`type`
		ORDER BY
			CASE
		WHEN RDT LIKE 'UOT' THEN
			1
		WHEN RDT LIKE 'MAL' THEN
			2
		WHEN RDT LIKE 'PMC' THEN
			3
		ELSE
			4
		END, RDT
	) AS X
GROUP BY
	x.RDT,
	x.type WITH ROLLUP;
+-----+---------------+--------+-------+--------+------------+
| RDT | type          | number | total | perc   | thedate    |
+-----+---------------+--------+-------+--------+------------+
| MAL | checking      | 112    |  3249 | 3.40   | 2014-03-27 |
| MAL | control       | 33     |  3249 | 1.00   | 2014-03-27 |
| MAL | reconstructed | 5      |  3249 | 0.20   | 2014-03-27 |
| MAL | regular       | 960    |  3249 | 29.50  | 2014-03-27 |
| MAL | study         | 10     |  3249 | 0.30   | 2014-03-27 |
| MAL | tot           | 1120   |  3249 | 34.40  | 2014-03-27 |
| PMC | checking      | 107    |  3153 | 3.40   | 2014-03-27 |
| PMC | control       | 167    |  3153 | 5.30   | 2014-03-27 |
| PMC | reconstructed | 8      |  3153 | 0.30   | 2014-03-27 |
| PMC | regular       | 833    |  3153 | 26.40  | 2014-03-27 |
| PMC | study         | 72     |  3153 | 2.30   | 2014-03-27 |
| PMC | tot           | 1187   |  3153 | 37.70  | 2014-03-27 |
| RAS | checking      | 1      |   970 | 0.10   | 2014-03-27 |
| RAS | control       | 42     |   970 | 4.30   | 2014-03-27 |
| RAS | reconstructed | 1      |   970 | 0.10   | 2014-03-27 |
| RAS | regular       | 318    |   970 | 32.80  | 2014-03-27 |
| RAS | study         | 3      |   970 | 0.30   | 2014-03-27 |
| RAS | tot           | 365    |   970 | 37.60  | 2014-03-27 |
| UOT | checking      | 11     |  3527 | 0.30   | 2014-03-27 |
| UOT | control       | 283    |  3527 | 8.00   | 2014-03-27 |
| UOT | regular       | 235    |  3527 | 6.70   | 2014-03-27 |
| UOT | study         | 8      |  3527 | 0.20   | 2014-03-27 |
| UOT | tot           | 537    |  3527 | 15.20  | 2014-03-27 |
| tot | tot           | 3209   |  3527 | 124.90 | 2014-03-27 |
+-----+---------------+--------+-------+--------+------------+
24 rows in set

mysql> 
ORDER BY FIND_IN_SET(rdt,'UOT,MAL,PMC,RAS')

I’m sorry but new order by clause not work:

mysql> SELECT
	IFNULL(`RDT`, 'tot') AS RDT,
	IFNULL(`type`, 'tot') AS `type`,
	SUM(`number`) AS `number`,
	`total`,
	SUM(`perc`) AS `perc`,
	`thedate`
FROM
	(
		SELECT
			IFNULL(`RDT`, 'tot') AS RDT,
			`type`,
			`NUMBER`,
			`total`,
			`perc`,
			`THEDATE`
		FROM
			`dotable`
		GROUP BY
			`RDT`,
			`type`
		ORDER BY
			FIND_IN_SET(rdt, 'UOT,MAL,PMC,RAS')
	) AS X
GROUP BY
	x.RDT,
	x.type WITH ROLLUP;
+-----+---------------+--------+-------+--------+------------+
| RDT | type          | number | total | perc   | thedate    |
+-----+---------------+--------+-------+--------+------------+
| MAL | checking      | 112    |  3249 | 3.40   | 2014-03-27 |
| MAL | control       | 33     |  3249 | 1.00   | 2014-03-27 |
| MAL | reconstructed | 5      |  3249 | 0.20   | 2014-03-27 |
| MAL | regular       | 960    |  3249 | 29.50  | 2014-03-27 |
| MAL | study         | 10     |  3249 | 0.30   | 2014-03-27 |
| MAL | tot           | 1120   |  3249 | 34.40  | 2014-03-27 |
| PMC | checking      | 107    |  3153 | 3.40   | 2014-03-27 |
| PMC | control       | 167    |  3153 | 5.30   | 2014-03-27 |
| PMC | reconstructed | 8      |  3153 | 0.30   | 2014-03-27 |
| PMC | regular       | 833    |  3153 | 26.40  | 2014-03-27 |
| PMC | study         | 72     |  3153 | 2.30   | 2014-03-27 |
| PMC | tot           | 1187   |  3153 | 37.70  | 2014-03-27 |
| RAS | checking      | 1      |   970 | 0.10   | 2014-03-27 |
| RAS | control       | 42     |   970 | 4.30   | 2014-03-27 |
| RAS | reconstructed | 1      |   970 | 0.10   | 2014-03-27 |
| RAS | regular       | 318    |   970 | 32.80  | 2014-03-27 |
| RAS | study         | 3      |   970 | 0.30   | 2014-03-27 |
| RAS | tot           | 365    |   970 | 37.60  | 2014-03-27 |
| UOT | checking      | 11     |  3527 | 0.30   | 2014-03-27 |
| UOT | control       | 283    |  3527 | 8.00   | 2014-03-27 |
| UOT | regular       | 235    |  3527 | 6.70   | 2014-03-27 |
| UOT | study         | 8      |  3527 | 0.20   | 2014-03-27 |
| UOT | tot           | 537    |  3527 | 15.20  | 2014-03-27 |
| tot | tot           | 3209   |  3527 | 124.90 | 2014-03-27 |
+-----+---------------+--------+-------+--------+------------+
24 rows in set

mysql> 

ORDER BY in a subquery makes no logical sense whatsoever

ORDER BY should be in the outer query only

Okey Sir, but if ORDER BY on the outer query only I’ve error:

mysql> SELECT
	IFNULL(`RDT`, 'tot') AS RDT,
	IFNULL(`type`, 'tot') AS `type`,
	SUM(`number`) AS `number`,
	`total`,
	SUM(`perc`) AS `perc`,
	`thedate`
FROM
	(
		SELECT
			IFNULL(`RDT`, 'tot') AS RDT,
			`type`,
			`NUMBER`,
			`total`,
			`perc`,
			`THEDATE`
		FROM
			`dotable`
		GROUP BY
			`RDT`,
			`type`
	) AS X
GROUP BY
	x.RDT,
	x.type WITH ROLLUP
ORDER BY
	FIND_IN_SET(RDT, 'UOT,MAL,PMC,RAS');
1221 - Incorrect usage of CUBE/ROLLUP and ORDER BY
mysql> 

gee, i wonder if there’s anything you could do about that, like putting the function into the SELECT clause…

I don’t understand this …

this thread started out by you asking for how to order your values

i gave you the FIND_IN_SET function

you put it into the ORDER BY clause of a subquery

i said ORDER BY makes sense only in the outer query

you discovered that you cannot have an ORDER BY clause along with ROLLUP

i suggested you put the function into the SELECT clause

think it over some more, please

Okay, I’m trying this without success …


mysql> SELECT
	*
FROM
	(
		SELECT
			FIND_IN_SET(RDT, 'UOT,MAL,PMC,RAS,tot') AS myOrder,
			IFNULL(`RDT`, 'tot') AS RDT,
			IFNULL(`type`, 'rdt tot') AS `type`,
			SUM(`number`) AS `number`,
			`total`,
			SUM(`perc`) AS `perc`,
			`thedate`
		FROM
			(
				SELECT
					IFNULL(`RDT`, 'tot') AS RDT,
					`type`,
					`NUMBER`,
					`total`,
					`perc`,
					`THEDATE`
				FROM
					`dotable`
				GROUP BY
					`RDT`,
					`type`
			) AS X
		GROUP BY
			x.RDT,
			x.type WITH ROLLUP
	) y
ORDER BY
	myOrder;
+---------+-----+---------------+--------+-------+--------+------------+
| myOrder | RDT | type          | number | total | perc   | thedate    |
+---------+-----+---------------+--------+-------+--------+------------+
| NULL    | tot | rdt tot       | 3209   |  3527 | 124.90 | 2014-03-27 |
|       1 | UOT | study         | 8      |  3527 | 0.20   | 2014-03-27 |
|       1 | UOT | control       | 283    |  3527 | 8.00   | 2014-03-27 |
|       1 | UOT | rdt tot       | 537    |  3527 | 15.20  | 2014-03-27 |
|       1 | UOT | regular       | 235    |  3527 | 6.70   | 2014-03-27 |
|       1 | UOT | checking      | 11     |  3527 | 0.30   | 2014-03-27 |
|       2 | MAL | study         | 10     |  3249 | 0.30   | 2014-03-27 |
|       2 | MAL | reconstructed | 5      |  3249 | 0.20   | 2014-03-27 |
|       2 | MAL | checking      | 112    |  3249 | 3.40   | 2014-03-27 |
|       2 | MAL | rdt tot       | 1120   |  3249 | 34.40  | 2014-03-27 |
|       2 | MAL | regular       | 960    |  3249 | 29.50  | 2014-03-27 |
|       2 | MAL | control       | 33     |  3249 | 1.00   | 2014-03-27 |
|       3 | PMC | regular       | 833    |  3153 | 26.40  | 2014-03-27 |
|       3 | PMC | control       | 167    |  3153 | 5.30   | 2014-03-27 |
|       3 | PMC | study         | 72     |  3153 | 2.30   | 2014-03-27 |
|       3 | PMC | reconstructed | 8      |  3153 | 0.30   | 2014-03-27 |
|       3 | PMC | checking      | 107    |  3153 | 3.40   | 2014-03-27 |
|       3 | PMC | rdt tot       | 1187   |  3153 | 37.70  | 2014-03-27 |
|       4 | RAS | study         | 3      |   970 | 0.30   | 2014-03-27 |
|       4 | RAS | reconstructed | 1      |   970 | 0.10   | 2014-03-27 |
|       4 | RAS | checking      | 1      |   970 | 0.10   | 2014-03-27 |
|       4 | RAS | rdt tot       | 365    |   970 | 37.60  | 2014-03-27 |
|       4 | RAS | regular       | 318    |   970 | 32.80  | 2014-03-27 |
|       4 | RAS | control       | 42     |   970 | 4.30   | 2014-03-27 |
+---------+-----+---------------+--------+-------+--------+------------+
24 rows in set

mysql> 

you realize that nulls always sort first, right?

do you understand where the null is coming from?

I’m trying this with success but I dont no if is your suggestion …:

mysql> SELECT
	*
FROM
	(
		SELECT
			IFNULL(`RDT`, 'tot') AS RDT,
			IFNULL(`type`, 'rdt tot') AS `type`,
			SUM(`number`) AS `number`,
			`total`,
			SUM(`perc`) AS `perc`,
			`thedate`
		FROM
			(
				SELECT
					IFNULL(`RDT`, 'tot') AS RDT,
					`type`,
					`NUMBER`,
					`total`,
					`perc`,
					`THEDATE`
				FROM
					`dotable`
				GROUP BY
					`RDT`,
					`type`
			) AS X
		GROUP BY
			x.RDT,
			x.type WITH ROLLUP
	) y
ORDER BY
	FIND_IN_SET(RDT, 'UOT,MAL,PMC,RAS,tot,tot_rdt');
+-----+---------------+--------+-------+--------+------------+
| RDT | type          | number | total | perc   | thedate    |
+-----+---------------+--------+-------+--------+------------+
| UOT | regular       | 235    |  3527 | 6.70   | 2014-03-27 |
| UOT | checking      | 11     |  3527 | 0.30   | 2014-03-27 |
| UOT | study         | 8      |  3527 | 0.20   | 2014-03-27 |
| UOT | control       | 283    |  3527 | 8.00   | 2014-03-27 |
| UOT | rdt tot       | 537    |  3527 | 15.20  | 2014-03-27 |
| MAL | checking      | 112    |  3249 | 3.40   | 2014-03-27 |
| MAL | rdt tot       | 1120   |  3249 | 34.40  | 2014-03-27 |
| MAL | regular       | 960    |  3249 | 29.50  | 2014-03-27 |
| MAL | control       | 33     |  3249 | 1.00   | 2014-03-27 |
| MAL | study         | 10     |  3249 | 0.30   | 2014-03-27 |
| MAL | reconstructed | 5      |  3249 | 0.20   | 2014-03-27 |
| PMC | study         | 72     |  3153 | 2.30   | 2014-03-27 |
| PMC | reconstructed | 8      |  3153 | 0.30   | 2014-03-27 |
| PMC | checking      | 107    |  3153 | 3.40   | 2014-03-27 |
| PMC | rdt tot       | 1187   |  3153 | 37.70  | 2014-03-27 |
| PMC | regular       | 833    |  3153 | 26.40  | 2014-03-27 |
| PMC | control       | 167    |  3153 | 5.30   | 2014-03-27 |
| RAS | regular       | 318    |   970 | 32.80  | 2014-03-27 |
| RAS | control       | 42     |   970 | 4.30   | 2014-03-27 |
| RAS | study         | 3      |   970 | 0.30   | 2014-03-27 |
| RAS | reconstructed | 1      |   970 | 0.10   | 2014-03-27 |
| RAS | checking      | 1      |   970 | 0.10   | 2014-03-27 |
| RAS | rdt tot       | 365    |   970 | 37.60  | 2014-03-27 |
| tot | rdt tot       | 3209   |  3527 | 124.90 | 2014-03-27 |
+-----+---------------+--------+-------+--------+------------+
24 rows in set

mysql> 

are you happy with it?

not completely because:

  1. I would like your fundamental opinion;
  2. The last row of output is wrong in colum perc because show 124.90 value and not 90.98 value (numer/total)*100 = (3209/3527)*100
+-----+---------------+--------+-------+--------+------------+
| RDT | type          | number | total | perc   | thedate    |
+-----+---------------+--------+-------+--------+------------+
| tot | rdt tot       | 3209   |  3527 | 124.90 | 2014-03-27 |
+-----+---------------+--------+-------+--------+------------+
  1. my opinion is, you’d be better off not using ROLLUP for all the pain and grief it has caused you over the years

  2. no idea, man

it’s true the ROLLUP cause me stomach ache … :slight_smile: … but such alternative ?

alternative is to calculate the totals yourself in php (or whatever application language you’re using) while printing out the query results

Okay, many thanks.
Good luck