Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > select version(); > > <pre> > | version() | > | :-------- | > | 5.6.51 | > </pre> <!-- --> > CREATE TABLE contact_info > ( > id INT NOT NULL, > pri_contact_id INT, > sec_contact_id INT, > blah_blah VARCHAR(32) > ); > > <pre> > ✓ > </pre> <!-- --> > 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'); > > <pre> > ✓ > </pre> <!-- --> > SELECT * FROM contact_info ORDER BY blah_blah, (- pri_contact_id) DESC; > > <pre> > id | pri_contact_id | sec_contact_id | blah_blah > -: | -------------: | -------------: | :-------- > 1 | 1 | <em>null</em> | Team A > 2 | 1 | 3 | Team A > 4 | 1 | <em>null</em> | Team A > 5 | 1 | 2 | Team A > 3 | 4 | 2 | Team B > </pre> <!-- --> > SELECT * FROM contact_info ORDER BY blah_blah, (0 - pri_contact_id) DESC; > > <pre> > id | pri_contact_id | sec_contact_id | blah_blah > -: | -------------: | -------------: | :-------- > 1 | 1 | <em>null</em> | Team A > 2 | 1 | 3 | Team A > 4 | 1 | <em>null</em> | Team A > 5 | 1 | 2 | Team A > 3 | 4 | 2 | Team B > </pre> <!-- --> > SELECT * FROM contact_info ORDER BY blah_blah, ISNULL(sec_contact_id) ASC; > > <pre> > id | pri_contact_id | sec_contact_id | blah_blah > -: | -------------: | -------------: | :-------- > 2 | 1 | 3 | Team A > 5 | 1 | 2 | Team A > 1 | 1 | <em>null</em> | Team A > 4 | 1 | <em>null</em> | Team A > 3 | 4 | 2 | Team B > </pre> <!-- --> > SELECT * FROM contact_info ORDER BY blah_blah, ISNULL(sec_contact_id) DESC; > > <pre> > id | pri_contact_id | sec_contact_id | blah_blah > -: | -------------: | -------------: | :-------- > 1 | 1 | <em>null</em> | Team A > 4 | 1 | <em>null</em> | Team A > 2 | 1 | 3 | Team A > 5 | 1 | 2 | Team A > 3 | 4 | 2 | Team B > </pre> <!-- --> > -- > -- CORRECT RESULT - but MySQL specific: > -- > SELECT * FROM contact_info ORDER BY blah_blah, ISNULL(sec_contact_id), sec_contact_id ASC; > > <pre> > id | pri_contact_id | sec_contact_id | blah_blah > -: | -------------: | -------------: | :-------- > 5 | 1 | 2 | Team A > 2 | 1 | 3 | Team A > 1 | 1 | <em>null</em> | Team A > 4 | 1 | <em>null</em> | Team A > 3 | 4 | 2 | Team B > </pre> <!-- --> > -- > -- 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; > > <pre> > id | pri_contact_id | sec_contact_id | blah_blah > -: | -------------: | -------------: | :-------- > 5 | 1 | 2 | Team A > 2 | 1 | 3 | Team A > 1 | 1 | <em>null</em> | Team A > 4 | 1 | <em>null</em> | Team A > 3 | 4 | 2 | Team B > </pre> <!-- --> > > > > SELECT * FROM contact_info ORDER BY blah_blah, sec_contact_id IS NULL DESC > > > <pre> > id | pri_contact_id | sec_contact_id | blah_blah > -: | -------------: | -------------: | :-------- > 1 | 1 | <em>null</em> | Team A > 4 | 1 | <em>null</em> | Team A > 2 | 1 | 3 | Team A > 5 | 1 | 2 | Team A > 3 | 4 | 2 | Team B > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=427a8a0dd63fe710ce949b03ae72ab69)*
back to fiddle