Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Conditional Order By Expand / Collapse
Author
Message
Posted Tuesday, November 16, 2010 7:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 2,127, Visits: 1,717
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).
Post #1021525
Posted Tuesday, November 16, 2010 9:35 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:26 AM
Points: 668, Visits: 485
Solved a similar situation in the past, but got tricked this time. Good question.
Thanks.
Post #1021595
Posted Tuesday, November 16, 2010 9:47 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, August 28, 2014 6:38 AM
Points: 1,494, Visits: 1,598
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.
Post #1021615
Posted Tuesday, November 16, 2010 10:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
Thanks for the question.
Post #1021632
Posted Tuesday, November 16, 2010 10:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 13,637, Visits: 10,523
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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1021639
Posted Tuesday, November 16, 2010 10:58 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:04 AM
Points: 3,675, Visits: 72,433
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
For tips on how to post your problems
Post #1021675
Posted Tuesday, November 16, 2010 12:16 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:34 AM
Points: 1,566, Visits: 1,851
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?
Post #1021736
Posted Tuesday, November 16, 2010 3:59 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:20 AM
Points: 974, Visits: 691
Good question, thanks for taking the time to help educate the SQL community.

Hope this helps,
Rich



Post #1021846
Posted Wednesday, November 17, 2010 1:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:41 AM
Points: 18, Visits: 146
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
Post #1021953
Posted Wednesday, November 17, 2010 3:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:24 AM
Points: 5,977, Visits: 8,239
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1022010
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse