How could you implement a custom sort that groups matching records from other source

Question

This is obviously simplified. I have 2 applications: Driver and Match. I'm presenting data to users in this order and asking them to select which of the people in the Match application is truly a match for the person in the Driver application. Data needs to be ordered as you see below: Driver rows need to be in alpha order by PersonName. Below each Driver record, list the Match records that have the same PersonMatchID, in PersonName alpha order. Some Driver records will have no corresponding Match records.

If you started with the rows below in random order, how would you write a SQL query to return them in this order? I am dealing with about 3000 rows at most, so I don't have to worry overmuch about optimization.

Source-system    PersonName    PersonPossibleMatchID
-------------    ----------    ---------------------
Driver           AA            77777
  Match          AA            77777
Driver           AB            11111
  Match          AA            11111
Driver           BB            33333
  Match          BA            33333
  Match          BB            33333
Driver           CC            99999
  Match          CB            99999
  Match          CC            99999
  Match          CD            99999
Driver           DD            44444
Driver           EE            22222

I've tried

SELECT * ORDER BY LEFT(PersonName,1), PersonPossibleMatchId, Source-system

but this doesn't return Driver records in strictly alpha order - you get all the A's first, but within that the order on second letter is randomized. For example, in the above list, I might have Driver AB listed prior to Driver AA.

Added: I'm using MS SQL (2012)

Answer 1

Here's another couple of possibilities

SELECT [Source-System],
       PersonName,
       PersonPossibleMatchID
FROM   #src
ORDER  BY MAX(CASE
                WHEN [Source-system] = 'Driver'
                  THEN PersonName
              END) OVER (PARTITION BY PersonPossibleMatchId) ASC,
          [Source-System] DESC,
          PersonName ASC 

(online demo)

- Or -

WITH DriverPersonNames
     AS (SELECT PersonPossibleMatchID,
                PersonName
         FROM   #src
         WHERE  [Source-System] = 'Driver')
SELECT s.*
FROM   #src s
       JOIN DriverPersonNames d
         ON d.PersonPossibleMatchID = s.PersonPossibleMatchID
ORDER  BY d.PersonName ASC,
          [Source-System] DESC,
          s.PersonName ASC

(online demo)

Both will return the same results assuming that there is exactly one Driver record per PersonPossibleMatchID and PersonPossibleMatchID is not nullable.

(DDL for online demos borrowed from jyao's answer)

Answer 2

Oracle solution:

SELECT sourcesystem, personname, personpossiblematchid
   , MAX(DECODE(SourceSystem,'Driver',personname, NULL)) 
      OVER (PARTITION BY PersonPossibleMatchID) driverpn 
FROM x ORDER BY 4,DECODE(sourcesystem,'Driver',1,2),3,2;

Data:

drop table x;
create table x (sourcesystem varchar2(10), personname varchar2(2), personpossiblematchid number(5));
insert into x values ('  Match','BB',33333);
insert into x values ('Driver','CC',99999);
insert into x values ('  Match','CB',99999);
insert into x values ('  Match','CC',99999);
insert into x values ('  Match','CD',99999);
insert into x values ('Driver','DD',44444);
insert into x values ('Driver','EE',22222);
insert into x values ('Driver','AA',77777);
insert into x values ('  Match','AA',77777);
insert into x values ('Driver','AB',11111);
insert into x values ('  Match','AA',11111);
insert into x values ('Driver','BB',33333);
insert into x values ('  Match','BA',33333);

commit;

Answer 3

Here is a solution (tested on SQL Server 2012)

-- create test table
if object_id('tempdb..#src', 'U') is not null
    drop table #src;
create table #src ([Source-System] varchar(10), PersonName char(2), PersonPossibleMatchID int)
go

-- poupulate tables with random data sequence
insert into #src ([Source-System], PersonName, PersonPossibleMatchID)
values 
 ('Driver','EE',  22222)
,('  Match','CC', 99999)
,('  Match','CB', 99999)
,('Driver','AA',  77777)
,('  Match','AA', 11111)
,('Driver','BB',  33333)
,('  Match','BA', 33333)
,('  Match','AA', 77777)
,('Driver','AB',  11111)
,('  Match','BB', 33333)
,('Driver','CC',  99999)
,('  Match','CD', 99999)
,('Driver','DD',  44444)
go

-- check here
select * from #src
go

-- solution here
; with c as 
(select top 3000  PersonName, PersonPossibleMatchID
 from #src
 where [Source-System]='Driver' order by PersonName)
select A.[Source-System], A.PersonName, A.PersonPossibleMatchID  from c
cross apply ( select top 3000 * from #src s 
              where s.[Source-System]='Driver' and s.PersonName = c.PersonName and s.PersonPossibleMatchID = c.PersonPossibleMatchID 
              order by [Source-System] asc,  personname asc
              union all
              select top 3000 * from #src s 
              where s.[Source-System]<> 'Driver' and  s.PersonPossibleMatchID = c.PersonPossibleMatchID 
              order by [Source-System] asc, personname asc) A([Source-System], PersonName, PersonPossibleMatchID )

Here is the result