Ordering Rows Using a User-Defined Order

By Sean Baird on 1 September 2000 | 5 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

Discuss this article: 5 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Dynamic ORDER BY (22 January 2001)

Other Recent Forum Posts

return exception depending on the condition (0 Replies)

overload in sql a server Processs is taking much t (4 Replies)

simple query run very slow (3 Replies)

Create Alias Table and inner join it to itself (6 Replies)

SQL Code Help (3 Replies)

production issue need to replicate and resolve (2 Replies)

An item with the same key has already been added. (1 Reply)

how to make a script sql with results like this (X (1 Reply)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -