20 comments

• 

  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.
  reply
• 

  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
  reply
• 

  John

  2 years ago

  I like it Lamprey!
  reply
• 

  Sumit Thomas

  2 years ago

  Excellent post! A very clean way of writing SPs with dynamic order by.
  reply
• 

  Steve

  2 years ago

  Absolute magic - very well explained and saved me a real headache. Thanks a million!
  reply
• 

  Ken

  1 year 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.
  reply
• 

  John

  1 year 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.
  reply
• 

  rob

  1 year ago

  Very nice work. A fun trick to look at too! Thanks for sharing...
  reply
• 

  RedLine

  1 year ago

  Great thanks!!! Very powerfull, clear and intelligence solution.
  reply
• 

  Francisco

  1 year ago

  Excellent way.- Thanks
  reply
• 

  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.
  reply
• 

  Uncle

  1 year ago

  Thank you soooo much. You saved my life! :)
  reply
• 

  Rash

  7 months ago

  The great job......................easily explained
  reply
• 

  john

  6 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
  reply
• 

  Mab

  5 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.
  reply
• 

  Alexander

  4 months ago

  Great work, thanks!
  reply
• 

  li

  3 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)
  reply
• 

  John Sheehan

  3 months ago

  Sorry, I don't know off hand. Try stackoverflow.com
  reply
• 

  Miguel_TX

  1 month 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
  reply
• 

  Miguel_TX

  1 month ago

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