clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2555292 fiddles created (37427 in the last week).

select version();
version()
5.6.51
 hidden batch(es)


CREATE TABLE contact_info ( id INT NOT NULL, pri_contact_id INT, sec_contact_id INT, blah_blah VARCHAR(32) );
 hidden batch(es)


INSERT INTO contact_info VALUES (1, 1, NULL, 'Team A'), (2, 1, 3, 'Team A'), (3, 4, 2, 'Team B'), (4, 1, NULL, 'Team A'), (5, 1, 2, 'Team A');
 hidden batch(es)


SELECT * FROM contact_info ORDER BY blah_blah, (- pri_contact_id) DESC;
id pri_contact_id sec_contact_id blah_blah
1 1 Team A
2 1 3 Team A
4 1 Team A
5 1 2 Team A
3 4 2 Team B
 hidden batch(es)


SELECT * FROM contact_info ORDER BY blah_blah, (0 - pri_contact_id) DESC;
id pri_contact_id sec_contact_id blah_blah
1 1 Team A
2 1 3 Team A
4 1 Team A
5 1 2 Team A
3 4 2 Team B
 hidden batch(es)


SELECT * FROM contact_info ORDER BY blah_blah, ISNULL(sec_contact_id) ASC;
id pri_contact_id sec_contact_id blah_blah
2 1 3 Team A
5 1 2 Team A
1 1 Team A
4 1 Team A
3 4 2 Team B
 hidden batch(es)


SELECT * FROM contact_info ORDER BY blah_blah, ISNULL(sec_contact_id) DESC;
id pri_contact_id sec_contact_id blah_blah
1 1 Team A
4 1 Team A
2 1 3 Team A
5 1 2 Team A
3 4 2 Team B
 hidden batch(es)


-- -- CORRECT RESULT - but MySQL specific: -- SELECT * FROM contact_info ORDER BY blah_blah, ISNULL(sec_contact_id), sec_contact_id ASC;
id pri_contact_id sec_contact_id blah_blah
5 1 2 Team A
2 1 3 Team A
1 1 Team A
4 1 Team A
3 4 2 Team B
 hidden batch(es)


-- -- CORRECT RESULT - works on PG as well! -- SELECT * FROM contact_info order by blah_blah, CASE WHEN (sec_contact_id IS NULL) THEN 0 ELSE 1 END DESC, sec_contact_id ASC;
id pri_contact_id sec_contact_id blah_blah
5 1 2 Team A
2 1 3 Team A
1 1 Team A
4 1 Team A
3 4 2 Team B
 hidden batch(es)


SELECT * FROM contact_info ORDER BY blah_blah, sec_contact_id IS NULL DESC
id pri_contact_id sec_contact_id blah_blah
1 1 Team A
4 1 Team A
2 1 3 Team A
5 1 2 Team A
3 4 2 Team B
 hidden batch(es)