DISQUS

Just Sayin' More Words: Slightly more dynamic ORDER BY in SQL Server 2005

  • Jared Roberts · 2 years ago
    This is a great post... I have been trying to figure out a good way to do this forever. Good work.
  • Lamprey · 2 years ago
    (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
  • John · 2 years ago
    I like it Lamprey!
  • Sumit Thomas · 2 years ago
    Excellent post! A very clean way of writing SPs with dynamic order by.
  • Steve · 2 years ago
    Absolute magic - very well explained and saved me a real headache. Thanks a million!
  • Ken · 2 years ago
    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.
  • John · 2 years ago
    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.
  • rob · 2 years ago
    Very nice work. A fun trick to look at too! Thanks for sharing...
  • RedLine · 2 years ago
    Great thanks!!! Very powerfull, clear and intelligence solution.
  • Francisco · 2 years ago
    Excellent way.- Thanks
  • demvin01 · 1 year ago
    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.
  • Uncle · 1 year ago
    Thank you soooo much. You saved my life! :)
  • Rash · 11 months ago
    The great job......................easily explained
  • john · 10 months ago
    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
  • Mab · 9 months ago
    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.
  • Alexander · 8 months ago
    Great work, thanks!
  • li · 7 months ago
    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)
  • John Sheehan · 7 months ago
    Sorry, I don't know off hand. Try stackoverflow.com
  • Miguel_TX · 5 months ago
    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
  • Miguel_TX · 5 months ago
    I forgot to mention that this way should also work in SQL Server 2000 for anyone still using that.