Ordering Rows Using a User-Defined Order
By Sean Baird
on 01 September 2000
| 3 Comments
| 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
