How to sort GUID's the SQL Server way using Delphi

Question

In a project I am working on it would be nice if I can sort an in memory list of guids and compare against a SQL server table ordered by these same guids. Unfortunately when an ordered list is returned by SQL Server, the order is not immediately apparent.

What would be the best way to sort this in memory list so that the order is the same as would be returned by SQL Server?

For example, a query "SELECT ID FROM TABLE1 ORDER BY ID" returns:

A46030EC-BF3A-4F7C-88CC-00117DBC1A52
159A0A9D-18B7-4D6C-ABB3-005FAB666D91
3C58CFC5-1829-481C-9686-007CE71132B8
15A96D5F-DAFB-4EF1-9202-00B201CE5151
BCFDE733-0AB0-483F-B912-00BF93F6FA7E
6CC06558-7670-4879-9D3F-00CB3D3649BD

For the sake of this question, assume I have an array of strings which contain the guid values above, however in the following order:

159A0A9D-18B7-4D6C-ABB3-005FAB666D91
15A96D5F-DAFB-4EF1-9202-00B201CE5151
6CC06558-7670-4879-9D3F-00CB3D3649BD
8A9325AF-A84E-4BDB-AFA8-C9D09D7FC064
A46030EC-BF3A-4F7C-88CC-00117DBC1A52
BCFDE733-0AB0-483F-B912-00BF93F6FA7E

I want to see that I am missing one from the SQL query, and have one that the SQL query did not have. The quickest way is obviously to have both lists sorted the same way, but I do not want to perform a query such as "SELECT ID FROM TABLE1 ORDER BY cast(ID as varchar(100))" but would rather fix the sort on the Delphi side.

Answer 1

Alberto Ferrari has written a blog entry about it. From there:

0 1 2 3  4 5  6 7  8 9  A B C D E F
00000000-0000-0000-0000-010000000000

• 0..3 are evaluated in left to right order and are the less important, then
• 4..5 are evaluated in left to right order, then
• 6..7 are evaluated in left to right order, then
• 8..9 are evaluated in right to left order, then
• A..F are evaluated in right to left order and are the most important