Ordering Rows Using a User-Defined Order

By Sean Baird on 1 September 2000 | Tags: ORDER BY

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