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

YSLGuru

SSC-Insane

Points: 21661
More actions
June 26, 2024 at 9:57 pm

#4431724
How To Work-A-Round Not being able too use CASE within ORDER BY with differing data types
GOAL: To create a query that will change it's Order By based on choices a user makes
PROBLEM: Trying to do this with a CASE doesn't work because you can't use a CONSTANT value in the CASE statement. In this specific case it's not actually a CONSTANT because when the program that uses this query executes it, the constant named '#sOrderBy1#' is replaced with 1 of 2 possible choices the user makes; it's a placeholder. I moved the CASE to the SELECT and set the ORDER BY to sort based on position but because the 2 possible values are of differencing data types I had to convert the NUMERIC data type to VARCHAR. That change allows the query to work but the sorting is off because SQL sorts it like text so the value 10 would come before 2 or 3.
Any thoughts on how to work-around this?
Thanks
QUERY:
```
  SELECT DISTINCT 
         TST.hMy,  
         TST.sCode, 
         CASE '#sOrderBy1#' WHEN 'hMy' THEN Convert(VARCHAR,TST.hMy) ELSE TST.sCode END
         
  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
```
Kindest Regards,
Just say No to Facebook!

Viewing 10 posts - 1 through 9 (of 9 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 5 months, 4 weeks ago by Dennis Jensen.

Phil Parkin SSC Guru Points: 246985 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;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ScottPletcher SSC Guru Points: 101120 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: 1004406 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: 21661 More actions · Answer 5

Thanks for replying

Kindest Regards,

Just say No to Facebook!

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

Thanks for replying Phil

Kindest Regards,

Just say No to Facebook!

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

Scott - That worked perfectly. Thanks!

Kindest Regards,

Just say No to Facebook!

YSLGuru SSC-Insane Points: 21661 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: 1004406 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)