Return to Ordering Rows Using a User-Defined Order

Ordering Rows Using a User-Defined Order

Written by Sean Baird on 01 September 2000

Jonathan writes: "How would I go about ordering results using a specified order in order to offset and limit the results. Say I have non-unique ids, (23, 12, 98, 3) and I need to return rows that contain these ids in the order I specified above. How could I do this without using any extra queries or sub-queries?

Jonathan -

Thankfully, you didn't say "How can I do this without using any extra tables", because that would have been trickier :o)

Since SQL doesn't natively provide this sort of ordering, you need to use another table that contains your desired key order and join to it. Something like this:

CREATE TABLE Sorter (
      KeyID int,
      SortOrder int
)

INSERT Sorter VALUES (23,1)
INSERT Sorter VALUES (12,2)
INSERT Sorter VALUES (98,3)
INSERT Sorter VALUES (3,4)

SELECT ID, yada
FROM foo
      JOIN Sorter
            ON foo.ID = Sorter.KeyID
ORDER BY Sorter.SortOrder

That should do the trick.
-SQLGuru