How To Work-A-Round Not being able too use CASE within ORDER BY

Question

How To Work-A-Round Not being able too use CASE within ORDER BY

Viewing 9 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Dennis Jensen SSChasing Mays Points: 606 More actions · Answer 1

Okay well the problem seems to be within your CASE statement and not the conversion out of your case statement the question is what is the largets numeric value that you could get within TST.hMy?

Second question is "WHEN 'hMy' " what is this actually supposed to be checking? Is it checking for ISNUMERIC(TST.hMy) or are you really checking to see if the 'hMy' is 'hMy' which technically I am going to guess is always True since you make no comparison whatsoever within the WHEN clause.

Still if you just use FORMAT rather than CONVERT you can change the Numeric value into a legitimate alphanumeric sortable value assuming you know the biggest numeric number you could get which is to say if it has 6 digits then FORMAT(TST.hMy, '000000').

This reply was modified 1 years, 4 months ago by Dennis Jensen.

Phil Parkin SSC Guru Points: 247183 More actions · Answer 2

You can use CASE in ORDER BY and you can get variable sorting to work. You can also get round the varchar/numeric issue by using an expression in the ORDER BY. Here is an example:

DECLARE @Sort TINYINT = 2;

SELECT TOP(1000)
       c.name
      ,c.system_type_id
FROM sys.columns c
ORDER BY CASE @Sort
             WHEN 1 THEN
                 c.name
             WHEN 2 THEN
                 RIGHT(CONCAT ('00000000000', CAST (c.system_type_id AS NVARCHAR(100))), 5)
             ELSE
                 NULL
         END;

ScottPletcher SSC Guru Points: 101207 More actions · Answer 3

Or take advantage of SQL's inherent capabilities by using the sql_variant data type (yes, technically you don't need the second CAST):

SELECT DISTINCT 
         TST.hMy,  
         TST.sCode, 
         CASE '#sOrderBy1#' WHEN 'hMy' THEN CAST(TST.hMy AS sql_variant) --<<--
                            ELSE CAST(TST.sCode AS sql_variant) END AS order_by --<<--
         
  FROM TSTASK TST LEFT OUTER JOIN TSSTEP TSTS         ON TST.hMy = TSTS.hTSTask
                  LEFT OUTER JOIN TSSTEPTEMPLATE TSTT ON TSTS.hTSStepTemplate = TSTT.hMy
  WHERE 1 = 1
 
  ORDER BY 3

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 4

If you use FORMAT anyplace where there are a lot of rows, I can only wish you luck for performance because you're going to need it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

YSLGuru SSC-Insane Points: 21669 More actions · Answer 5

Thanks for replying

Kindest Regards,

Just say No to Facebook!

YSLGuru SSC-Insane Points: 21669 More actions · Answer 6

Thanks for replying Phil

Kindest Regards,

Just say No to Facebook!

YSLGuru SSC-Insane Points: 21669 More actions · Answer 7

Scott - That worked perfectly. Thanks!

Kindest Regards,

Just say No to Facebook!

YSLGuru SSC-Insane Points: 21669 More actions · Answer 8

Jeff - I'm going with Scotts recommendation which sticks with CAST and not FORMAT. That said, just out of curiosity do you know how much data/rows need to be involved for FORMAT to cause performance issues? In my case the source table is only a few hundred rows as it's like a lookup table but we do have other tables that measure in the 10s to 100's of millions of rows

Thanks for replying

Kindest Regards,

Just say No to Facebook!

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 9

YSLGuru wrote:

Jeff - I'm going with Scotts recommendation which sticks with CAST and not FORMAT. That said, just out of curiosity do you know how much data/rows need to be involved for FORMAT to cause performance issues? In my case the source table is only a few hundred rows as it's like a lookup table but we do have other tables that measure in the 10s to 100's of millions of rows
Thanks for replying

FORMAT is always worse than CONVERT or CAST for performance. Based on low number of rows, people will justify the use of FORMAT. If that small thing with the small number of rows is used a lot, then you just added another cut to the "SQL Death by a Thousand Cuts".

And, you cannot control how much someone will use a piece of code in the future and you certainly can't control who uses such code "examples" for a heck of a lot more rows or a much higher call frequency.

Remember... a whole lot of performance issues aren't big issues on their own but, when combined with hundreds of supposed small issues, you end up with a constant din of performance and resource usage.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)