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)

  • I would say this can be a good question for interviewing a senior sql server DBA/Developer candidate. – jyao Aug 9 '16 at 20:36
  • 2
    You may want to merge your accounts – mustaccio Aug 10 '16 at 1:09

Here's another couple of possibilities

SELECT [Source-System],
FROM   #src
                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,
         FROM   #src
         WHERE  [Source-System] = 'Driver')
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)

| improve this answer | |

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;


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);

| improve this answer | |
  • Well they are certainly not on Oracle because of LEFT. – Martin Smith Aug 9 '16 at 22:01
  • @MartinSmith They could be on Oracle, but would have to be using a custom function for LEFT, which isn't likely. Good catch. – Leigh Riffel Aug 10 '16 at 12:33

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)

-- poupulate tables with random data sequence
insert into #src ([Source-System], PersonName, PersonPossibleMatchID)
 ('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)

-- check here
select * from #src

-- 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

enter image description here

| improve this answer | |
  • 3
    I hadn't looked at this closely but this doesn't return any guaranteed order at all. Order by in CTEs and cross applies don't guarantee anything about the overall order of results. The only thing that does that is an order by at the outer level of the query. Any order that you observe without that is execution plan dependant and not guaranteed. – Martin Smith Aug 9 '16 at 21:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.