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. 2555285 fiddles created (37422 in the last week).

select version();
version
PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
 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');
5 rows affected
 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)


-- -- CORRECT RESULT - in line with SQL Standard -- SELECT * FROM contact_info ORDER BY blah_blah, sec_contact_id NULLS LAST;
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 MySQL 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)