Order by with grouping on 2nd column

Question

Been trying to solve this but without success.

Here's my table

SN      PID
----    ---------
1       NULL
2       1000005
3       NULL
4       1000002
5       1000005
6       NULL
7       1000002
8       NULL
9       1000005

I need the sorting to be by SN but when PID is not null, it needs to group them together. Hence the result i'm looking for is

SN      PID
----    ---------
1       NULL
2       1000005
5       1000005    -- group together with the previous SAME PID
9       1000005    -- continue to group together
3       NULL       -- continue to the next unused SN (no more PID = 1000005)
4       1000002
7       1000002    -- group together with the previous SAME PID
6       NULL       -- continue to the next unused SN
8       NULL

Appreciate anyone's advice. Thanks!

Answer 1

I would solve this by sorting with an artificial column that is equal to the MIN SN of all the rows with the same PID, or equal to the SN when PID is null.

SELECT *,
 CASE 
   WHEN PID IS NULL THEN SN
   ELSE (SELECT MIN(t2.SN) FROM MyTable t2 WHERE t2.PID=t1.PID)
 END AS sortby
FROM MyTable t1
ORDER BY sortby, SN

If you need to exclude sortby in the output, you can either use the above as a CTE, or you can plug the CASE expression directly into the ORDER BY and leave it out of the SELECT list.