Conditional Order By

Question

Conditional Order By

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

OCTom SSChampion Points: 11785 More actions · Answer 1

I got the question correct but not because I knew what I was doing. I haven't written T-SQL for very long and don't write complex code. I looked at the CASE statements in the ORDER BY and didn't know you could do that. It looked funny, so, I picked none of the above. :w00t:

Tha could come in handy some day. Thanks.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 2

I don't understand the implicit conversion. Why is it happening?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Daniel Bowlin (11/16/2010)
I don't understand the implicit conversion. Why is it happening?

Hi Daniel, good question!

Microsoft http://msdn.microsoft.com/en-us/library/ms190309.aspx
When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.
SQL Server uses the following precedence order for data types:
user-defined data types (highest)
sql_variant
xml
datetimeoffset
datetime2
datetime
smalldatetime
date
time
float
real
decimal
money
smallmoney
bigint
int
smallint
tinyint
bit
ntext
text
image
timestamp
uniqueidentifier
nvarchar (including nvarchar(max) )
nchar
varchar (including varchar(max) )
char
varbinary (including varbinary(max) )
binary (lowest)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

Thanks to those of you who appreciate this question.

To those who think it had unnecessary "tricks" in it, I did try several variants of this question and they all ended up being too easy.

The "tricks" were deliberate and designed to check whether you would notice the implicit conversion and whether you even knew it would happen.

If you don't like the question, I am sorry - for those that like to be challenged to check every small detail I think it provides a sufficient level of complexity to give you a good challenge!

Have a nice day!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 5

mister.magoo (11/16/2010)
Thanks to those of you who appreciate this question.
To those who think it had unnecessary "tricks" in it, I did try several variants of this question and they all ended up being too easy.
The "tricks" were deliberate and designed to check whether you would notice the implicit conversion and whether you even knew it would happen.
If you don't like the question, I am sorry - for those that like to be challenged to check every small detail I think it provides a sufficient level of complexity to give you a good challenge!
Have a nice day!

I agree! Tricks train the eyes to see bugs quickly! 🙂

Richard Warr SSCertifiable Points: 6967 More actions · Answer 6

ronmoses (11/16/2010)You shouldn't complain about people who complain about spelling on the part of people who complain about describing questions as "nonsense." Too many nested complaints will make your thread difficult to read and maintain. Instead, those complaints should be broken out into their own threads and called from the main thread.
😛
ron

What we need here is a recursive CTE (Complaint Tracking Engine).

Surii Default port Points: 1497 More actions · Answer 7

Solved a similar situation in the past, but got tricked this time. Good question.

Thanks.

Tony++ SSCarpal Tunnel Points: 4421 More actions · Answer 8

I liked the question, I've had to debug a piece of code that was doing this technique. While not failing from a illegal datatype conversion, it was doing the wrong sort because the implicit conversion yielded the wrong value.

Good question, it's a helpful (if not particularly fast) sorting technique.

I've found this useful in the SQL behind an SSRS report. The report parameters lets the user choose the sort, the report passes this into the SQL, and the SQL lets the database do the sort.

UMG Developer SSChampion Points: 13482 More actions · Answer 9

UMG Developer

SSChampion

Points: 13482

November 16, 2010 at 10:12 am

#1251450

Thanks for the question.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 10

Tony++ (11/16/2010)
I've found this useful in the SQL behind an SSRS report. The report parameters lets the user choose the sort, the report passes this into the SQL, and the SQL lets the database do the sort.

That seems indeed very useful. I'm going to remember that 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mtassin SSC-Insane Points: 23099 More actions · Answer 11

mister.magoo (11/16/2010)
Thanks to those of you who appreciate this question.
To those who think it had unnecessary "tricks" in it, I did try several variants of this question and they all ended up being too easy.
The "tricks" were deliberate and designed to check whether you would notice the implicit conversion and whether you even knew it would happen.

Heh I saw this

DECLARE @sortby VARCHAR(10)

SET @sortby='String2'

SELECT String1, String2, Date1

FROM (

SELECT 'StringValue1', '2', CONVERT(datetime,'1 July 2010 00:01:00')

UNION ALL

SELECT 'StringValue2', '1', CONVERT(datetime,'2 July 2010 00:02:00')

)

Realized that there weren't field names for the various portions of the select and assumed it would error out.

I didn't see this at the end of the above portion of the query

AS a(String1,String2,Date1)

as a practice I've always named my columns in a sub-select/union type situation and expected an error similar to

Msg 8155, Level 16, State 2, Line 9

No column name was specified for column 1 of

So I got it right, but for the wrong reason. :/

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 12

Well, put me down as someone who did not know you could use CASE in the ORDER BY clause, although now that I've seen it, it makes sense. I can definitely make use of this.

Thanks for the good question.

Although, now that I've thought about it a bit more, it seems to me that the CASE clause could be a performance hit?

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 13

Good question, thanks for taking the time to help educate the SQL community.

Hope this helps,
Rich

[p]
[/p]

Oliveoil SSC Veteran Points: 268 More actions · Answer 14

Thanks a lot for the question. I wasn't able to answer it but reading the answers I know now that I can use CASE in an ORDER BY 😀

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 15

I really liked the subject matter of the question - mixing data types in an ORDER BY is an error I see being made lots of times, and it's not always easy to pinpoint - especially when the data used in test just happens to never cause problems (it happens! trust me)

What I disliked was the weak obfuscation attempt. The question would have been much better with

WHEN @sortby = 'String1' THEN String1

WHEN @sortby = 'String2' THEN String2

WHEN @sortby = 'Date1' THEN Date1

Now, people may get it wrong because they were led astray by the weird reversal of column names. I don't buy the argument by the question author that this would have been "too simple" - if that really were the case (which I don't believe), the question should not have been submitted at all. In my opinion, QotD is about testing -and, even more important, gaining- SQL skills. Not reading skills. Without the reversal, the question would have been a solid test of whether people know about the dangers of using CASE in ORDER BY, and thus an excellent question. With the reversal, it's still a good question - good, not excellent.

That being said, I do hope to see more questions from Mister Magoo in the future.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/