| Author |
Topic |
|
SMerrill
Posting Yak Master
206 Posts |
 Posted - 2005-03-18 : 01:41:30
|
| I was bit by an interview question recently:"How would you sort either by Lastname, Firstname (or by Firstname, Lastname) depending upon what the user wanted?"I answered that I would use dynamic sql and construct the ORDER BY clause on the fly depending upon a parameter. The interviewer asked if there were any other ways I could think to do it without resorting to dynamic SQL.I was stumped.She divulged that the answer she was searching for was to put a SELECT CASE statement within the ORDER BY clause.Now, according to BOL, I can't seem to find any reference to the fact that this syntax is even legal.Has anyone else heard of it?~ Shaun MerrillSeattle, WA |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-03-18 : 03:24:52
|
| MS SQL Help filesUSE pubsGOSELECT 'Price Category' = CASE WHEN price IS NULL THEN 'Not yet priced' WHEN price < 10 THEN 'Very Reasonable Title' WHEN price >= 10 and price < 20 THEN 'Coffee Table Title' ELSE 'Expensive book!' END, CAST(title AS varchar(20)) AS 'Shortened Title'FROM titlesORDER BY priceGO |
 |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-03-18 : 04:26:07
|
| She meant:use pubsgodeclare @i intset @i=1select au_id, au_lname, au_fnamefrom dbo.authorsorder by case when @i=1 then au_lname else au_fname end |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-03-18 : 07:06:41
|
| oops...missed word 'either' >> either by Lastname, Firstnameuse pubsgodeclare @i intset @i=1select au_id, au_lname, au_fnamefrom dbo.authorsorder by case when @i=1 thenau_lname+au_fname else au_fname+au_lname end |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-03-18 : 08:33:25
|
| But this will take more time than without case in the Order by ClauseMadhivananFailing to plan is Planning to fail |
|
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2005-03-22 : 14:05:33
|
| Stoad: Thank you . . . This is the perfect answer. Afrika: This is not a dynamic sort. I am suprised that there is no example of putting an ORDER BY CASE...END clause in the BOL which is the definitive T-SQL reference book.~ Shaun MerrillSeattle, WA |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-22 : 14:31:58
|
quote:
Originally posted by SMerrill
Stoad: Thank you . . . This is the perfect answer. Afrika: This is not a dynamic sort. I am suprised that there is no example of putting an ORDER BY CASE...END clause in the BOL which is the definitive T-SQL reference book.~ Shaun MerrillSeattle, WA
Don't be confused that there is some special "ORDER BY CASE" clause available. There isn't. ORDER BY simply accepts expressions in the list of what it can order by, and a CASE construct simply is an expression. So it doesn't have to be explicitly stated that you can use CASE in an order by -- it is implied since you can use CASE anywhere you can construct an expression.Despite the odd syntax it uses, remember that CASE is really just a function that returns a value.- Jeff |
|
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2005-03-24 : 20:43:49
|
| I realized that. I guess I was thinking that this would have been rather freaky syntax: ORDER BY CASE WHEN @I=1 THEN au_lname, au_fname ELSE au_fname, au_lname ENDThanx.~ Shaun MerrillSeattle, WA |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-24 : 20:47:46
|
| It is freaky syntax, and it doesnt' work. CASE only returns ONE expression. You'd have to do something like this:ORDER BY CASE WHEN @I=1 THEN au_lname ELSE au_fname END,CASE WHEN @I=1 THEN au_fname ELSE au_lname END |
|
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2005-03-24 : 20:54:38
|
| ROFL ... thanks!~ Shaun MerrillSeattle, WA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
An Interview Question