MySQL 5.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


5.6.51


CREATE TABLE contact_info
(
  id INT NOT NULL,
  pri_contact_id INT,
  sec_contact_id INT,
  blah_blah VARCHAR(32)
);
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');
SELECT * FROM contact_info ORDER BY blah_blah, (- pri_contact_id) DESC;


id
pri_contact_id
sec_contact_id
blah_blah


1
1
null
Team A

2
1
3
Team A

4
1
null
Team A

5
1
2
Team A

3
4
2
Team B


SELECT * FROM contact_info ORDER BY blah_blah, (0 - pri_contact_id) DESC;


id
pri_contact_id
sec_contact_id
blah_blah


1
1
null
Team A

2
1
3
Team A

4
1
null
Team A

5
1
2
Team A

3
4
2
Team B


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
null
Team A

4
1
null
Team A

3
4
2
Team B


SELECT * FROM contact_info ORDER BY blah_blah, ISNULL(sec_contact_id) DESC;


id
pri_contact_id
sec_contact_id
blah_blah


1
1
null
Team A

4
1
null
Team A

2
1
3
Team A

5
1
2
Team A

3
4
2
Team B


-- 
-- 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
null
Team A

4
1
null
Team A

3
4
2
Team B


-- 
-- 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
null
Team A

4
1
null
Team A

3
4
2
Team B




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
null
Team A

4
1
null
Team A

2
1
3
Team A

5
1
2
Team A

3
4
2
Team B