snippetsqlMinor
How could you implement a custom sort that groups matching records from other source
Viewed 0 times
groupsimplementyousourcerecordscouldothercustomthathow
Problem
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.
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)
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 22222I'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)
Solution
Here's another couple of possibilities
(online demo)
(online demo)
Both will return the same results assuming that there is exactly one Driver record per
(DDL for online demos borrowed from jyao's answer)
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)
Code Snippets
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 ASCWITH 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 ASCContext
StackExchange Database Administrators Q#146312, answer score: 4
Revisions (0)
No revisions yet.