Select with conditional order

Databases
1

Hi all, I need your help.

I need order my mysql table with:

  1. field P DESC;
  2. field Date_A ASC;
  3. field Date_B ASC, if field Date_B IS NOT NULL.

I try this query, but I have error:

SELECT `P`
       , `Date_A`
       , `Date_B`
         FROM table
 ORDER BY 
         `P` DESC
       , `Date_A` ASC
          CASE WHEN `Date_B` IS NOT NULL
          THEN ORDER BY `Date_B` ASC

Can you help me?

2

What is your error?

3

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘CASE WHEN Date_B IS NOT NULL THEN ORDER BY Date_B ASC’ at line 6

4 

SELECT `P`
, `Date_A`
, `Date_B`
FROM table
ORDER BY 
`P` DESC
, `Date_A` ASC
,  CASE WHEN `Date_B` IS NOT NULL
THEN ORDER BY `Date_B` ASC

You was missing an , on the 3rd line of the ORDER BY clause

5

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ORDER BY Date_B ASC’ at line 9

:rolleyes:

6

you can’t stuff the ORDER BY clause into the THEN part of a CASE expression

try this –


ORDER 
    BY p DESC 
     , Date_A ASC 
     , CASE WHEN Date_B IS NULL
            THEN 'humpty'
            ELSE 'dumpty' END
     , Date_B

:slight_smile:

7

Ok, thanks… now the query working… :slight_smile:

But I have this output:

ID	P	DATE_A	        DATE_B
1	555	30/07/2010	03/05/2010
2	555	30/07/2010	03/05/2010
3	435	05/04/2010	13/02/2009
4	435	05/04/2010	16/10/2009
5	435	05/04/2010	25/06/2010
6	338	01/02/2011	
7	250	01/09/2010	13/05/2008
8	250	01/09/2010	23/11/2009
9	250	01/09/2010	18/01/2011
10	224	23/09/2010	21/01/2011
11	224	01/02/2011	
12	180	02/02/2011	
13	175	12/01/2011	
14	170	02/02/2011	15/03/2010
15	163	01/11/2010	
16	150	20/02/2010

Why I see the rows numbers 6, 11, 12, 13, 15 and 16 if the Date_B IS NULL?

8

you see them because you did not make it clear that you wanted to omit them from the results

SELECT p
     , Date_A
     , Date_B   
  FROM daTable 
 WHERE Date_B IS NOT NULL
ORDER 
    BY p` DESC 
     , Date_A ASC 
     , Date_B ASC
9

If you are wanting to include it even if it is null, you don’t need tto check if it is…

10

Sorry… but I don’t have omit the Date_B if IS NULL… I need order my mysql table with:

  1. field P DESC;
  2. field Date_A ASC;
  3. field Date_B ASC, if field Date_B IS NOT NULL.

And I think this output… I wrong?:

ID	P	DATE_A	        DATE_B
1	555	30/07/2010	03/05/2010
2	555	30/07/2010	03/05/2010
3	435	05/04/2010	13/02/2009
4	435	05/04/2010	16/10/2009
5	435	05/04/2010	25/06/2010
7	250	01/09/2010	13/05/2008
8	250	01/09/2010	23/11/2009
9	250	01/09/2010	18/01/2011
10	224	23/09/2010	21/01/2011
14	170	02/02/2011	15/03/2010
6	338	01/02/2011
11	224	01/02/2011	
12	180	02/02/2011	
13	175	12/01/2011
15	163	01/11/2010	
16	150	20/02/2010
11

that output does ~not~ have p DESC as the major sort column

so you are doing something else that i don’t understand

12

You are right… it’s possible orber by P DESC when Date_B IS NOT NULL?
I need see the rows with date_B IS NULL, but before Date_B is NOT NULL…

13 
ORDER 
    BY p DESC 
     , Date_A ASC 
     , Date_B ASC
14

Ok…

SELECT ID, `P`
, `Date_A`
, `Date_B`
FROM daTable 
ORDER 
    BY p DESC 
     , Date_A ASC 
     , Date_B ASC

The output:


ID	P	DATE_A	        DATE_B
1	555	2010-07-30	2010-05-03
2	555	2010-07-30	2010-05-03
3	435	2010-04-05	2009-02-13
4	435	2010-04-05	2009-10-16
5	435	2010-04-05	2010-06-25
6	338	2011-02-01	
7	250	2010-09-01	2008-05-13
8	250	2010-09-01	2009-11-23
9	250	2010-09-01	2011-01-18
10	224	2010-09-23	2011-01-21
11	224	2011-02-01	
12	180	2011-02-02	
13	175	2011-01-12	
14	170	2011-02-02	2010-03-15
15	163	2010-11-01	
16	150	2010-02-20	

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50144
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50144
File Encoding         : 65001

Date: 2011-02-25 19:03:54
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `daTable `
-- ----------------------------
DROP TABLE IF EXISTS `daTable `;
CREATE TABLE `daTable ` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `P` int(11) DEFAULT NULL,
  `DATE_A` date DEFAULT NULL,
  `DATE_B` date DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of daTable 
-- ----------------------------
INSERT INTO daTable  VALUES ('1', '555', '2010-07-30', '2010-05-03');
INSERT INTO daTable  VALUES ('2', '555', '2010-07-30', '2010-05-03');
INSERT INTO daTable  VALUES ('3', '435', '2010-04-05', '2009-02-13');
INSERT INTO daTable  VALUES ('4', '435', '2010-04-05', '2009-10-16');
INSERT INTO daTable  VALUES ('5', '435', '2010-04-05', '2010-06-25');
INSERT INTO daTable  VALUES ('6', '338', '2011-02-01', null);
INSERT INTO daTable  VALUES ('7', '250', '2010-09-01', '2008-05-13');
INSERT INTO daTable  VALUES ('8', '250', '2010-09-01', '2009-11-23');
INSERT INTO daTable  VALUES ('9', '250', '2010-09-01', '2011-01-18');
INSERT INTO daTable  VALUES ('10', '224', '2010-09-23', '2011-01-21');
INSERT INTO daTable  VALUES ('11', '224', '2011-02-01', null);
INSERT INTO daTable  VALUES ('12', '180', '2011-02-02', null);
INSERT INTO daTable  VALUES ('13', '175', '2011-01-12', null);
INSERT INTO daTable  VALUES ('14', '170', '2011-02-02', '2010-03-15');
INSERT INTO daTable  VALUES ('15', '163', '2010-11-01', null);
INSERT INTO daTable  VALUES ('16', '150', '2010-02-20', null);
15

the output is correct

please explain which row you think is not in the correct sequence

16

Try to explain:

  1. I need output where extract all rows of the table daTable;

  2. In the daTable the some values of field Date_B IS NULL;

  3. I need order the rows of daTable in this sequence:

    • P DESC and Date_A ASC when Date_B IS NOT NULL

Mind of output:

ID	P	DATE_A	        DATE_B
1	555	30/07/2010	03/05/2010
2	555	30/07/2010	03/05/2010
3	435	05/04/2010	13/02/2009
4	435	05/04/2010	16/10/2009
5	435	05/04/2010	25/06/2010
7	250	01/09/2010	13/05/2008
8	250	01/09/2010	23/11/2009
9	250	01/09/2010	18/01/2011
10	224	23/09/2010	21/01/2011
14	170	02/02/2011	15/03/2010
6	338	01/02/2011
11	224	01/02/2011	
12	180	02/02/2011	
13	175	12/01/2011
15	163	01/11/2010	
16	150	20/02/2010

The rows numbers 1,2,3,4,5,7,8,9,10 and 14 are order by P DESC and Date_A ASC when Date_B IS NOT NULL.

The rows numbers 6,11,12,13,15 and 16 are order by P DESC and Date_A ASC but have Date_B IS NULL… and should go to the end… :x

17 
ORDER 
    BY CASE WHEN Date_B IS NULL
            THEN 'humpty'
            ELSE 'dumpty' END
     , p DESC 
     , Date_A ASC 
     , Date_B ASC
18

Many thanks for your help… and good bye r937! :blush: