Order by and case when

hi there, hope in your help.

I’ve this query, I need this particular order because when the value of field ISEG is GCL the value of field INTR is null even if it is valued.

I’ve tried with syntax order by case when but without success… :confused:

Can you help me?
thank you.

mysql> SELECT
	CONCAT(
		DATE_FORMAT(DayOfEvent, '%d/%m/%Y'),
		' ',
		HourOfEvent
	) AS `Day_event`,
	CASE
WHEN (
	ii = 'CBT GDM, PS'
	OR NCLBTDIS = 1
) THEN
	'GCL'
WHEN (LENGTH(CCA) = 6 AND CTI = 'DV') THEN
	'SNG'
ELSE
	ISEG
END AS ISEG,
 INTR
FROM
	tbl_m20
ORDER BY
	INTR DESC,
	`Day_event` ASC;

+------------------+--------------+
| Day_event        | ISEG  | INTR |
+------------------+--------------+
| 05/03/2013 11:33 | ASL   |  204 |
| 05/03/2013 10:06 | GIG   |    0 |
| 05/03/2013 10:15 | GIC   |    0 |
| 05/03/2013 10:26 | SNG   |    0 |
| 05/03/2013 10:28 | SNG   |    0 |
| 05/03/2013 10:28 | SNG   |    0 |
| 05/03/2013 11:02 | SNG   |    0 |
| 05/03/2013 12:19 | GCL   |   28 |
| 05/03/2013 12:48 | SNG   |    0 |
| 05/03/2013 14:01 | SNG   |    0 |
| 05/03/2013 14:15 | GIG   |    0 |
| 05/03/2013 14:16 | SNG   |    0 |
| 05/03/2013 14:19 | GCL   |  598 |
| 05/03/2013 15:50 | GIG   |    0 |
| 05/03/2013 16:02 | GIC   |    0 |
| 05/03/2013 16:15 | GIG   |    0 |
| 05/03/2013 16:18 | SNG   |    0 |
| 05/03/2013 18:20 | GIG   |    0 |
| 05/03/2013 23:38 | GIG   |    0 |
+------------------+-------+------+
19 rows in set

I would probably just do it this way…


SELECT Day_Event
	 , ISEG
	 , INTR
  FROM (SELECT CONCAT(DATE_FORMAT(DayOfEvent, '%d/%m/%Y'), ' ', HourOfEvent) AS Day_event
			 , CASE WHEN (ii = 'CBT GDM, PS' OR NCLBTDIS = 1) THEN	'GCL'
					WHEN (LENGTH(CCA) = 6 AND CTI = 'DV') THEN	'SNG'
					ELSE ISEG
			   END AS ISEG
			 , INTR
		  FROM tbl_m20) SQ
ORDER BY ISEG DESC
	   , INTR DESC
	   , Day_event ASC;

Hi Dave, I’m really happy for your quickly answer.

Unfortunately your suggestion produces wrong output, I’ve this:


+------------------+--------------+
| Day_event        | ISEG  | INTR |
+------------------+--------------+
| 05/03/2013 10:26 | SNG   |    0 |
| 05/03/2013 10:28 | SNG   |    0 |
| 05/03/2013 10:28 | SNG   |    0 |
| 05/03/2013 11:02 | SNG   |    0 |
| 05/03/2013 12:48 | SNG   |    0 |
| 05/03/2013 14:01 | SNG   |    0 |
| 05/03/2013 14:16 | SNG   |    0 |
| 05/03/2013 16:18 | SNG   |    0 |
| 05/03/2013 10:15 | GIC   |    0 |
| 05/03/2013 14:15 | GIG   |    0 |
| 05/03/2013 16:02 | GIC   |    0 |
| 05/03/2013 14:19 | GCL   |  598 |
| 05/03/2013 10:06 | GIG   |    0 |
| 05/03/2013 12:19 | GCL   |   28 |
| 05/03/2013 23:38 | GIG   |    0 |
| 05/03/2013 16:15 | GIG   |    0 |
| 05/03/2013 11:33 | ASL   |  204 |
| 05/03/2013 18:20 | GIG   |    0 |
| 05/03/2013 15:50 | GIG   |    0 |
+------------------+-------+------+
19 rows in set

I need this:


+------------------+--------------+
| Day_event        | ISEG  | INTR |
+------------------+--------------+
| 05/03/2013 11:33 | ASL   |  204 |
| 05/03/2013 10:06 | GIG   |    0 |
| 05/03/2013 10:15 | GIC   |    0 |
| 05/03/2013 10:26 | SNG   |    0 |
| 05/03/2013 10:28 | SNG   |    0 |
| 05/03/2013 10:28 | SNG   |    0 |
| 05/03/2013 11:02 | SNG   |    0 |
| 05/03/2013 12:19 | GCL   |   28 |
| 05/03/2013 12:48 | SNG   |    0 |
| 05/03/2013 14:01 | SNG   |    0 |
| 05/03/2013 14:15 | GIG   |    0 |
| 05/03/2013 14:16 | SNG   |    0 |
| 05/03/2013 14:19 | GCL   |  598 |
| 05/03/2013 15:50 | GIG   |    0 |
| 05/03/2013 16:02 | GIC   |    0 |
| 05/03/2013 16:15 | GIG   |    0 |
| 05/03/2013 16:18 | SNG   |    0 |
| 05/03/2013 18:20 | GIG   |    0 |
| 05/03/2013 23:38 | GIG   |    0 |
+------------------+-------+------+
19 rows in set

Can you please post the output of a SHOW CREATE TABLE query for the tbl_m20 table?

Of course.

mysql> SELECT Day_Event
	 , ISEG
	 , INTR
  FROM (SELECT CONCAT(DATE_FORMAT(DayOfEvent, '%d/%m/%Y'), ' ', HourOfEvent) AS Day_event
			 , CASE WHEN (ii = 'CBT GDM, PS' OR NCLBTDIS = 1) THEN	'GCL'
					WHEN (LENGTH(CCA) = 6 AND CTI = 'DV') THEN	'SNG'
					ELSE ISEG
			   END AS ISEG
			 , INTR
		  FROM tbl_m20) SQ
ORDER BY ISEG DESC
	   , INTR DESC
	   , Day_event ASC;
+------------------+------+------+
| Day_Event        | ISEG | INTR |
+------------------+------+------+
| 05/03/2013 10:26 | SNG  |    0 |
| 05/03/2013 10:28 | SNG  |    0 |
| 05/03/2013 10:28 | SNG  |    0 |
| 05/03/2013 11:02 | SNG  |    0 |
| 05/03/2013 12:48 | SNG  |    0 |
| 05/03/2013 14:01 | SNG  |    0 |
| 05/03/2013 14:16 | SNG  |    0 |
| 05/03/2013 16:18 | SNG  |    0 |
| 05/03/2013 10:06 | GIG  |    0 |
| 05/03/2013 14:15 | GIG  |    0 |
| 05/03/2013 15:50 | GIG  |    0 |
| 05/03/2013 16:15 | GIG  |    0 |
| 05/03/2013 18:20 | GIG  |    0 |
| 05/03/2013 23:38 | GIG  |    0 |
| 05/03/2013 10:15 | GIC  |    0 |
| 05/03/2013 16:02 | GIC  |    0 |
| 05/03/2013 14:19 | GCL  |  598 |
| 05/03/2013 12:19 | GCL  |   28 |
| 05/03/2013 11:33 | ASL  |  204 |
+------------------+------+------+
19 rows in set

mysql>


-- ----------------------------
-- Table structure for `tbl_m20`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_m20`;
CREATE TABLE `tbl_m20` (
  `DayOfEvent` date DEFAULT NULL,
  `HourOfEvent` varchar(255) DEFAULT NULL,
  `ISEG` varchar(255) DEFAULT NULL,
  `INTR` int(10) DEFAULT NULL,
  `ii` varchar(255) DEFAULT NULL,
  `NCLBTDIS` int(10) DEFAULT NULL,
  `CCA` varchar(255) DEFAULT NULL,
  `CTI` varchar(255) DEFAULT NULL,
  `id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tbl_m20
-- ----------------------------
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '10:06', 'GIG', '0', '', '0', '', '', '1');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '10:15', 'GIC', '0', '', '0', '', '', '2');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '10:26', '', '0', '', '0', 'AECIUM', 'DV', '3');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '10:28', '', '0', '', '0', 'AALIIS', 'DV', '4');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '10:28', '', '0', '', '0', 'ABASIA', 'DV', '5');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '11:02', '', '0', '', '0', 'ABELIA', 'DV', '6');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '11:33', 'ASL', '204', '', '0', '', '', '7');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '12:19', '', '28', 'CBT GDM, PS', '0', '', '', '8');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '12:48', '', '0', '', '0', 'ACAJOU', 'DV', '9');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '14:01', '', '0', '', '0', 'ABELIA', 'DV', '10');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '14:15', 'GIG', '0', '', '0', '', '', '11');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '14:16', '', '0', '', '0', 'ABULIA', 'DV', '12');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '14:19', '', '598', '', '1', '', '', '13');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '15:50', 'GIG', '0', '', '0', '', '', '14');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '16:02', 'GIC', '0', '', '0', '', '', '15');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '16:15', 'GIG', '0', '', '0', '', '', '16');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '16:18', '', '0', '', '0', 'ACACIA', 'DV', '17');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '18:20', 'GIG', '0', '', '0', '', '', '18');
INSERT INTO `tbl_m20` VALUES ('2013-03-05', '23:38', 'GIG', '0', '', '0', '', '', '19');

How is that possible? It’s sorting that in a totally different order than what the order by clause says…

@r937, what boneheaded thing did I do now???

:confused: :shifty:

i’ve been involved in dozens of cms9651’s threads, and they always either get way more confusing as they go along, or they start out that way

i’m not looking at any more of them, sorry

You have right, but in this case I’ve SHOW CREATE TABLE query for the tbl_m20 table.
You can try it yourself

Wait a minute…there’s no order in the set you desired. It’s not ordered by date - it’s not ordered by ISEG - it’s not ordered by INTR. You’re trying to order this by some other sort of arbitrary method. You need to explain much more clearly how this is supposed to be sorted, because your example makes no sense…


Ordered by Day_Event
+------------------+--------------+
| Day_event        | ISEG  | INTR |
+------------------+--------------+
| 05/03/2013 10:06 | GIG   |    0 |
| 05/03/2013 10:15 | GIC   |    0 |
| 05/03/2013 10:26 | SNG   |    0 |
| 05/03/2013 10:28 | SNG   |    0 |
| 05/03/2013 10:28 | SNG   |    0 |
| 05/03/2013 11:02 | SNG   |    0 |
| 05/03/2013 11:33 | ASL   |  204 |
| 05/03/2013 12:19 | GCL   |   28 |
| 05/03/2013 12:48 | SNG   |    0 |
| 05/03/2013 14:01 | SNG   |    0 |
| 05/03/2013 14:15 | GIG   |    0 |
| 05/03/2013 14:16 | SNG   |    0 |
| 05/03/2013 14:19 | GCL   |  598 |
| 05/03/2013 15:50 | GIG   |    0 |
| 05/03/2013 16:02 | GIC   |    0 |
| 05/03/2013 16:15 | GIG   |    0 |
| 05/03/2013 16:18 | SNG   |    0 |
| 05/03/2013 18:20 | GIG   |    0 |
| 05/03/2013 23:38 | GIG   |    0 |
+------------------+-------+------+


ORDERED BY ISEG, then Day_Event


+------------------+--------------+
| Day_event        | ISEG  | INTR |
+------------------+--------------+
| 05/03/2013 11:33 | ASL   |  204 |
| 05/03/2013 12:19 | GCL   |   28 |
| 05/03/2013 14:19 | GCL   |  598 |
| 05/03/2013 10:15 | GIC   |    0 |
| 05/03/2013 16:02 | GIC   |    0 |
| 05/03/2013 10:06 | GIG   |    0 |
| 05/03/2013 14:15 | GIG   |    0 |
| 05/03/2013 15:50 | GIG   |    0 |
| 05/03/2013 16:15 | GIG   |    0 |
| 05/03/2013 18:20 | GIG   |    0 |
| 05/03/2013 23:38 | GIG   |    0 |
| 05/03/2013 10:26 | SNG   |    0 |
| 05/03/2013 10:28 | SNG   |    0 |
| 05/03/2013 10:28 | SNG   |    0 |
| 05/03/2013 11:02 | SNG   |    0 |
| 05/03/2013 12:48 | SNG   |    0 |
| 05/03/2013 14:01 | SNG   |    0 |
| 05/03/2013 14:16 | SNG   |    0 |
| 05/03/2013 16:18 | SNG   |    0 |
+------------------+-------+------+


ORDERED BY INTR DESC, then Day_Event


+------------------+--------------+
| Day_event        | ISEG  | INTR |
+------------------+--------------+
| 05/03/2013 14:19 | GCL   |  598 |
| 05/03/2013 11:33 | ASL   |  204 |
| 05/03/2013 12:19 | GCL   |   28 |
| 05/03/2013 10:06 | GIG   |    0 |
| 05/03/2013 10:15 | GIC   |    0 |
| 05/03/2013 10:26 | SNG   |    0 |
| 05/03/2013 10:28 | SNG   |    0 |
| 05/03/2013 10:28 | SNG   |    0 |
| 05/03/2013 11:02 | SNG   |    0 |
| 05/03/2013 12:48 | SNG   |    0 |
| 05/03/2013 14:01 | SNG   |    0 |
| 05/03/2013 14:15 | GIG   |    0 |
| 05/03/2013 14:16 | SNG   |    0 |
| 05/03/2013 15:50 | GIG   |    0 |
| 05/03/2013 16:02 | GIC   |    0 |
| 05/03/2013 16:15 | GIG   |    0 |
| 05/03/2013 16:18 | SNG   |    0 |
| 05/03/2013 18:20 | GIG   |    0 |
| 05/03/2013 23:38 | GIG   |    0 |
+------------------+-------+------+

thank you for help, I try to explain.
This is my initial order by INTR DESC, Day_event ASC:

ORDER BY
	INTR DESC,
	`Day_event` ASC;
+------------------+------+------+
| Day_event        | ISEG | INTR |
+------------------+------+------+
| 05/03/2013 14:19 | GCL  |  598 |
| 05/03/2013 11:33 | ASL  |  204 |
| 05/03/2013 12:19 | GCL  |   28 |
| 05/03/2013 10:06 | GIG  |    0 |
| 05/03/2013 10:15 | GIC  |    0 |
| 05/03/2013 10:26 | SNG  |    0 |
| 05/03/2013 10:28 | SNG  |    0 |
| 05/03/2013 10:28 | SNG  |    0 |
| 05/03/2013 11:02 | SNG  |    0 |
| 05/03/2013 12:48 | SNG  |    0 |
| 05/03/2013 14:01 | SNG  |    0 |
| 05/03/2013 14:15 | GIG  |    0 |
| 05/03/2013 14:16 | SNG  |    0 |
| 05/03/2013 15:50 | GIG  |    0 |
| 05/03/2013 16:02 | GIC  |    0 |
| 05/03/2013 16:15 | GIG  |    0 |
| 05/03/2013 16:18 | SNG  |    0 |
| 05/03/2013 18:20 | GIG  |    0 |
| 05/03/2013 23:38 | GIG  |    0 |
+------------------+------+------+
19 rows in set

When the value of field ISEG is GCL the value of field INTR is zero even if it is valued, e.g. in this cases:

+------------------+------+------+
| Day_event        | ISEG | INTR |
+------------------+------+------+
| 05/03/2013 14:19 | GCL  |  598 |
| 05/03/2013 12:19 | GCL  |   28 |
+------------------+------+------+

I need consider the value of field INTR equal to zero because the field ISEG is GCL value.

basically I need this sort:

+------------------+--------------+
| Day_event        | ISEG  | INTR |
+------------------+--------------+
| 05/03/2013 11:33 | ASL   |  204 |
| 05/03/2013 10:06 | GIG   |    0 |
| 05/03/2013 10:15 | GIC   |    0 |
| 05/03/2013 10:26 | SNG   |    0 |
| 05/03/2013 10:28 | SNG   |    0 |
| 05/03/2013 10:28 | SNG   |    0 |
| 05/03/2013 11:02 | SNG   |    0 |
| 05/03/2013 12:19 | GCL   |    0 |
| 05/03/2013 12:48 | SNG   |    0 |
| 05/03/2013 14:01 | SNG   |    0 |
| 05/03/2013 14:15 | GIG   |    0 |
| 05/03/2013 14:16 | SNG   |    0 |
| 05/03/2013 14:19 | GCL   |    0 |
| 05/03/2013 15:50 | GIG   |    0 |
| 05/03/2013 16:02 | GIC   |    0 |
| 05/03/2013 16:15 | GIG   |    0 |
| 05/03/2013 16:18 | SNG   |    0 |
| 05/03/2013 18:20 | GIG   |    0 |
| 05/03/2013 23:38 | GIG   |    0 |
+------------------+-------+------+
19 rows in set
  • First order by INTR DESC when the ISEG value is different to ‘GCL’
  • Second order by Day_event ASC

Solved. :slight_smile:

mysql> SELECT
	CONCAT(
		DATE_FORMAT(DayOfEvent, '%d/%m/%Y'),
		' ',
		HourOfEvent
	) AS `Day_event`,
	CASE
WHEN (
	ii = 'CBT GDM, PS'
	OR NCLBTDIS = 1
) THEN
	'GCL'
WHEN (LENGTH(CCA) = 6 AND CTI = 'DV') THEN
	'SNG'
ELSE
	ISEG
END AS ISEG,
 INTR
FROM
	tbl_m20
ORDER BY
	CASE
WHEN (
	ii = 'CBT GDM, PS'
	OR NCLBTDIS = 1
) THEN
INTR = 0
ELSE INTR END DESC,
	`Day_event` ASC;
+------------------+------+------+
| Day_event        | ISEG | INTR |
+------------------+------+------+
| 05/03/2013 11:33 | ASL  |  204 |
| 05/03/2013 10:06 | GIG  |    0 |
| 05/03/2013 10:15 | GIC  |    0 |
| 05/03/2013 10:26 | SNG  |    0 |
| 05/03/2013 10:28 | SNG  |    0 |
| 05/03/2013 10:28 | SNG  |    0 |
| 05/03/2013 11:02 | SNG  |    0 |
| 05/03/2013 12:19 | GCL  |   28 |
| 05/03/2013 12:48 | SNG  |    0 |
| 05/03/2013 14:01 | SNG  |    0 |
| 05/03/2013 14:15 | GIG  |    0 |
| 05/03/2013 14:16 | SNG  |    0 |
| 05/03/2013 14:19 | GCL  |  598 |
| 05/03/2013 15:50 | GIG  |    0 |
| 05/03/2013 16:02 | GIC  |    0 |
| 05/03/2013 16:15 | GIG  |    0 |
| 05/03/2013 16:18 | SNG  |    0 |
| 05/03/2013 18:20 | GIG  |    0 |
| 05/03/2013 23:38 | GIG  |    0 |
+------------------+------+------+
19 rows in set

But that’s NOT sorting correctly…if it was then the three records with values for INTR would come first, followed by the zeroes. (598, 204, 28, 0,0,0,etc.)

Never mind - read your previous post - makes no sense to be honest since it looks like there’s no order - but it’s your site… :shifty: