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