DISQUS

DISQUS Hello! Just Sayin' More Words is using DISQUS, a powerful comment system, to manage its comments. Learn more.

Community Page

Just Sayin' More Words

A blog by John Sheehan
Jump to original thread »
Author

Slightly more dynamic ORDER BY in SQL Server 2005

Started by John Sheehan · 10 months ago

Dynamically ordering SELECT statements in T-SQL has been an issue I’ve run into a lot. The options have traditionally been use dynamic SQL (eww) or a CASE statement in the ORDER BY. CASE kind of works, but its limited (only one column, no sort directions, datatype issues, e ... Continue reading »

20 comments

  • This is a great post... I have been trying to figure out a good way to do this forever. Good work.
  • (NOTE: If you want to output the RANK() result and order by the same value, you have to specify the whole line twice as SQL server doesn’t appear to be accepting dynamically created columns as ORDER BY parameters). Here are the results:

    Almost, you can get the rank back without specifing it twice:

    CREATE PROCEDURE CustomerGetFinal
    @CustomerTypeID int
    AS
    BEGIN
    SELECT
    CustomerID,
    FirstName,
    LastName,
    CustomerTypeID,
    CASE
    WHEN @CustomerTypeID = 1 THEN (RANK() OVER (ORDER BY FirstName, LastName))
    WHEN @CustomerTypeID = 2 THEN (RANK() OVER (ORDER BY LastName, FirstName))
    WHEN @CustomerTypeID = 3 THEN (RANK() OVER (ORDER BY LastName DESC, FirstName DESC))
    END AS RankNumber
    FROM
    Customer
    WHERE
    CustomerTypeID = @CustomerTypeID
    ORDER BY
    RankNumber
    END
  • I like it Lamprey!
  • Excellent post! A very clean way of writing SPs with dynamic order by.
  • Absolute magic - very well explained and saved me a real headache. Thanks a million!
  • This is a neat way of doing dynamic sorting but performance on my query is very bad with this method. Granted it is a large query. But other methods out perform this.
  • Hi Ken,
    I kind of thought that would be the case for larger queries. I'm only using this method on a query for under 100 rows so I haven't run into any performance issues yet.
  • Very nice work. A fun trick to look at too! Thanks for sharing...
  • Great thanks!!! Very powerfull, clear and intelligence solution.
  • Excellent way.- Thanks
  • Great tip, thanks !

    About your sentence : "CASE kind of works, but its limited (only one column, no sort directions, datatype issues, etc)" I just want to say that you can control the direction with multiple CASE statements :

    Order By
    Case @orderby
    when 'DATEASC' then StartDate
    when 'NAMEASC' then LastName + FirstName
    end ASC,
    Case @orderby
    when 'DATEDESC' then StartDate
    when 'NAMEDESC' then LastName+ FirstName
    end DESC


    Also, I didn't run into any datatype problem in with the CASE, but I only tried with nvarchars and datetime, so I'm not sure about this.
  • Thank you soooo much. You saved my life! :)
  • The great job......................easily explained
  • An other alternative work around is not varchar columns casting sql_variant
    fitCount is int

    elect * from tFittest
    declare @sortCriteria varchar(50) set @sortCriteria = 'name'
    declare @sortDirection varchar(4) set @sortDirection = 'desc'


    select fitCount, name, wwid, @sortCriteria, @sortDirection, cast(fitCount as sql_variant)
    from tFittest
    order by
    case when @sortDirection = 'ASC' then
    case when @sortCriteria = 'fitCount' then (cast(fitCount as sql_variant) )
    when @sortCriteria = 'name' then (name )
    end
    end ASC,
    case when @sortDirection = 'DESC' then
    case when @sortCriteria = 'fitCount' then (cast(fitCount as sql_variant) )
    when @sortCriteria = 'name' then (name )
    end
    end DESC
  • Thanks for the article.
    Just a quick question. Is it possible to write this dynamic Order By with DISTINCT, as I am getting error with DISTINCT in my select statement.
  • Great work, thanks!
  • How can I use

    ORDER BY
    CASE @AccountOperator
    WHEN '>' THEN UserLastName
    END ASC,
    CASE @AccountOperator
    WHEN '<' THEN UserFirstName
    END DESC

    when UserLastName is an alias?(without using sub query)
  • Sorry, I don't know off hand. Try stackoverflow.com
  • I just tried this way of sorting and for the query I was running (a very big one) it took twice as long to run than it did with my own dynamic sorting.

    Everyone, please use this instead - it's faster when it counts:
    ORDER BY
    (CASE WHEN @CustomerTypeID = 1 THEN FirstName END),
    (CASE WHEN @CustomerTypeID = 1 THEN LastName END),
    (CASE WHEN @CustomerTypeID = 2 THEN LastName END),
    (CASE WHEN @CustomerTypeID = 2 THEN FirstName END),
    (CASE WHEN @CustomerTypeID = 3 THEN LastName END) DESC,
    (CASE WHEN @CustomerTypeID = 3 THEN FirstName END) DESC

    I ask that the author of this post (John Sheehan) try it and update this post with his findings if he finds that it works better. Thank you.

    I can't take credit for this way of doing it as I found it months ago on another posting - but I can't remember where.

    - Miguel_TX
  • I forgot to mention that this way should also work in SQL Server 2000 for anyone still using that.

Add New Comment

Returning? Login