ORDER BY, CASE, with multiple columns

I'm unable to specify multiple columns in my order by statement if i use a case statement.
Does anyone know why this is, or what syntax would make this work?

Thanks

SELECT ...
ORDER BY (CASE Lower(@.SortExpression)
WHEN 'prodname' THEN prodname, prodprice
WHEN 'prodsize' THEN prodsize, prodname
WHEN 'prodprice' THEN prodprice, prodname
Else prodcompany, prodname
END)

Also, i realize that in your order by statement, when you use CASE, all of your columns have to be the same data type.

So in ORDER BY clause above, i am attempting to order by "prodprice" as one of the possibilities. This produces the error:Error converting data type varchar to numeric.

The whole IDEA of a case statement is to avoid opening yourself to injection attacks by Dynamic Execution.

So...how can you use the case statement to order by multiple columns, and to order with different datatypes?|||Not sure it this helps butview post 386101 discusses something close to your question. Maybe the method discussed near the bottom can be adapted.|||Right, so you'd have something like this:

SET @.SortExpression = Lower(@.SortExpression)
SELECT ...
ORDER BY
CASE WHEN @.SortExpression = 'prodname' THEN prodname END,
CASE WHEN @.SortExpression = 'prodname' THEN prodprice END,
CASE WHEN @.SortExpression = 'prodsize' THEN prodsize END,
CASE WHEN @.SortExpression = 'prodsize' THEN prodname END,
CASE WHEN @.SortExpression = 'prodprice' THEN prodprice END,
CASE WHEN @.SortExpression = 'prodprice' THEN prodname END,
prodcompany,
prodname

I'm not exactly sure what that'll do to performance. It'd be worth it to see what the execution plan says.

Terri|||that alleviated some of my problem. Thanks.

How exactly do i test the excecution plan? or check to see if it's compiling all the way?

Can i use a SQL Trace?|||Check out this article:SQL Server Query Execution Plan Analysis.

Terri

ORDER BY parameter

How can I write that SORT depends on parameter, something like:
ORDER BY column1 case when @.param=0 then DESC else ASC end
Regards,SStandard SQL-92 does not allow you to use a function or expression in
an ORDER BY clause. The ORDER BY clause is part of a cursor and it can
only see the column names that appear in the SELECT clause list that
was used to build the result set. BP will now chime in that SQL-99
(officially called "a standard in progress" and not recognized by the
U.S. Government for actual use) does allow this.
But aside from this, there is the good programming practice of showing
the fields that are used for the sort to the user, usually on the left
side of each line since we read left to right.
The standard trick for picking a sorting order at run time is to use a
flag in CASE expression. If you want to sort on more than one column
and allow all possible combinations of sorting use one CASE per column:
SELECT
CASE @.flag_1
WHEN 'a' THEN CAST (a AS CHAR(n))
WHEN 'b' THEN CAST (b AS CHAR(n))
WHEN 'c' THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1,
CASE @.flag_2
WHEN 'x' THEN CAST (x AS CHAR(n))
WHEN 'y' THEN CAST (y AS CHAR(n))
WHEN 'z' THEN CAST (z AS CHAR(n))
ELSE NULL END AS sort_2,
..
CASE @.flag_n
WHEN 'n1' THEN CAST (n1 AS CHAR(n))
WHEN 'n2' THEN CAST (n2 AS CHAR(n))
WHEN 'n3' THEN CAST (n3 AS CHAR(n))
ELSE NULL END AS sort_2,
FROM Foobar
WHERE ...
ORDER BY sort_1, sort_2, ...
More than one sort column and only a limited set of combinations then
use concatenation.
CASE @.flag_1
WHEN 'ab'
THEN CAST(a AS CHAR(n)) ||' ' || CAST(b AS CHAR(n))
WHEN 'ba'
THEN CAST(b AS CHAR(n)) ||' ' || CAST(a AS CHAR(n))
ELSE NULL END AS sort_1,
If you need ASC and DESC options, then use a combination of CASE and
ORDER BY
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'ASC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'ASC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'ASC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_a,
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'DESC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'DESC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'DESC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_d
. ORDER BY sort_1_a ASC, sort_1_d DESC
I have shown explicit CAST(<exp> AS CHAR(n)), but if the datatypes of
the THEN clause expressions were already the same, there would be no
reason to force the conversions.
You change the ELSE NULL clause to any constant of the appropriate
datatype, but it should be something useful to the reader.
A neater way of doing this is to use one column for each sorting option
so you do not have worry about CAST() operations.
SELECT ...
CASE WHEN @.flag = 'a' THEN a ELSE NULL END AS sort1,
CASE WHEN @.flag = 'b' THEN b ELSE NULL END AS sort2,
CASE WHEN @.flag = 'c' THEN c ELSE NULL END AS sort3
FROM Foobar
WHERE ...
ORDER BY sort1, sort2, sort3;|||hi,
probably this link should answer your question:
http://www.aspfaq.com/2501
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"simon" wrote:

> How can I write that SORT depends on parameter, something like:
> ORDER BY column1 case when @.param=0 then DESC else ASC end
> Regards,S
>
>sql

Order By madness

I have a simple case statement which changes the "ORDER BY" clause based on
a parameter passed to a stored proc. 4 out of 5 cases work, but one fails
with the following:
Server: Msg 295, Level 16, State 3, Procedure pr_reportPartnersDBTest, Line
12
Syntax error converting character string to smalldatetime data type.
If I comment out the case block and put each ORDER BY as an independent
statement (uncommenting one at a time), they all work without error.
When the case block is used and the int 4 is passed (my zip field), I
receive the error message above. If I comment out the case statement and
just use "ORDER BY zip" it works fine.
I must be missing something obvious, but I'm too close and suspect I'm
missing something that someone else will immediately see.
All comments, ideas or thoughts welcomed.
-- CODE snippet --
select ...
from ...
where...
order by
case @.orderBy
when 1 then dateRegistered
when 2 then gender
when 3 then bodyType
when 4 then zip
when 5 then ageRange
else dateRegistered
end
-- The next lines are for debugging purposes...
-- I commented out the case block above, then tested each of the lines below
one at a time. They all work as expected. But the case statement above fails
when I pass it a 4 (zip). All other values work as expected.
-- order by dateRegistered
-- order by gender
-- order by bodyType
-- order by zip
-- order by ageRangeCASE in SQL Server is an expression that returns a scalar value of a
specific datatype. That means that if the separate expressions for each WHEN
have a different datatype, they will explicitly be converted to the datatype
with the highest datatype precedence, in your case smalldatetime. And you
can't convert all zipcodes into a smalldatetime value.
So the simplest way to do it is to have a separate CASE for each column:
order by
case @.orderBy when 2 then gender end,
case @.orderBy when 3 then bodyType end,
case @.orderBy when 4 then zip end,
case @.orderBy when 5 then ageRange end,
case when @.OrderBy NOT IN (2,3,4,5) then dateRegistered end
Jacco Schalkwijk
SQL Server MVP
"Don B" <DonBaarns@.hotmail.com> wrote in message
news:OlsEGQOUFHA.1432@.TK2MSFTNGP09.phx.gbl...
>I have a simple case statement which changes the "ORDER BY" clause based on
> a parameter passed to a stored proc. 4 out of 5 cases work, but one fails
> with the following:
> Server: Msg 295, Level 16, State 3, Procedure pr_reportPartnersDBTest,
> Line
> 12
> Syntax error converting character string to smalldatetime data type.
> If I comment out the case block and put each ORDER BY as an independent
> statement (uncommenting one at a time), they all work without error.
> When the case block is used and the int 4 is passed (my zip field), I
> receive the error message above. If I comment out the case statement and
> just use "ORDER BY zip" it works fine.
> I must be missing something obvious, but I'm too close and suspect I'm
> missing something that someone else will immediately see.
> All comments, ideas or thoughts welcomed.
> -- CODE snippet --
> select ...
> from ...
> where...
> order by
> case @.orderBy
> when 1 then dateRegistered
> when 2 then gender
> when 3 then bodyType
> when 4 then zip
> when 5 then ageRange
> else dateRegistered
> end
> -- The next lines are for debugging purposes...
> -- I commented out the case block above, then tested each of the lines
> below
> one at a time. They all work as expected. But the case statement above
> fails
> when I pass it a 4 (zip). All other values work as expected.
>
> -- order by dateRegistered
> -- order by gender
> -- order by bodyType
> -- order by zip
> -- order by ageRange
>|||It is also worth mention that a CASE is not allowed in the ORDER BY in
Standard SQL-92. You can put it in a column in the select list, name
it and use that name in the ORDER BY. This way your code will port, be
readable to people who do not speak T-SQL dialect and your user will be
able to tell what the sort was done on, instead of trying to guess.
In the olde days, we would print the sort key at both the left and
right sides of a print out, so you could lay a ruler acorss the 132
column page. The printout were pretty fuzzy at times and this really
helped. Today, I assume that the sort column would be on the right and
locked so you can scroll horizonally.|||Jacco,
EXCELLENT... I clearly don't know enough fundamentals about T-SQL. That
works great for my example.
I would really like a secondary sort on zip code (a varchar in my case)
which would be something like this:
order by zip, dateRegistered
and/or
order by bodyType, zip
The two statements above work as expected.
I can't just put the two columns with a comma inside the Then/End
combination as the case statement uses the comma as a delimiter. I don't
know how to escape it (or the equivalent) so the case statement can return a
set of columns for the order by clause. I may also want to change the
ASC/DESC order on specific columns but I assume if I figure out the syntax
for the multiple columns it will be similar if I add specific sort orders
for some columns.
All ideas welcomed!
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:O0KSlYOUFHA.2892@.TK2MSFTNGP14.phx.gbl...
> CASE in SQL Server is an expression that returns a scalar value of a
> specific datatype. That means that if the separate expressions for each
WHEN
> have a different datatype, they will explicitly be converted to the
datatype
> with the highest datatype precedence, in your case smalldatetime. And you
> can't convert all zipcodes into a smalldatetime value.
> So the simplest way to do it is to have a separate CASE for each column:
> order by
> case @.orderBy when 2 then gender end,
> case @.orderBy when 3 then bodyType end,
> case @.orderBy when 4 then zip end,
> case @.orderBy when 5 then ageRange end,
> case when @.OrderBy NOT IN (2,3,4,5) then dateRegistered end
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Don B" <DonBaarns@.hotmail.com> wrote in message
> news:OlsEGQOUFHA.1432@.TK2MSFTNGP09.phx.gbl...
on
fails
>|||Use the same approach:
order by
case @.orderBy when 1 then zip end,
case @.orderBy when 1 then dateRegistered end,
case @.orderBy when 2 then bodyType end,
case @.orderBy when 2 then zip end ASC,
case @.orderBy when 3 then bodyType end,
case @.orderBy when 3 then zip end DESC
Terri Morton
MVP - ASP/ASP.NET
"Don B" <DonBaarns@.hotmail.com> wrote in message
news:OrWlLtOUFHA.3716@.TK2MSFTNGP12.phx.gbl...
> Jacco,
> EXCELLENT... I clearly don't know enough fundamentals about T-SQL. That
> works great for my example.
> I would really like a secondary sort on zip code (a varchar in my case)
> which would be something like this:
> order by zip, dateRegistered
> and/or
> order by bodyType, zip
> The two statements above work as expected.
> I can't just put the two columns with a comma inside the Then/End
> combination as the case statement uses the comma as a delimiter. I don't
> know how to escape it (or the equivalent) so the case statement can return
> a
> set of columns for the order by clause. I may also want to change the
> ASC/DESC order on specific columns but I assume if I figure out the syntax
> for the multiple columns it will be similar if I add specific sort orders
> for some columns.
> All ideas welcomed!
>
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:O0KSlYOUFHA.2892@.TK2MSFTNGP14.phx.gbl...
> WHEN
> datatype

Order by Keyword ranking

I have the following query:
SELECT Distinct DatePosted,p.DateUpdated,Applied = Case when Email is NULL
then ' ' else '*' end,p.PositionID,JobTitle,JobDescription =
substring(JobDescription,1,200),Posted =
replace(convert(varchar,p.DatePosted,6), ' ',''),lastUpdated =
replace(convert(varchar,p.DateUpdated,6),' ',''), Company,Location FROM
ftsolutions..position p left outer join applicantPosition a on (a.PositionID
= p.PositionID and Email = 'tscheiderich@.yahoo.com') where p.ClientID = 1234
and Contains(jobDescription,'developer and sales') order by p.DateUpdated
DESC
I want to change the option to order by keyword ranking.
How would I do this?
Do I have to use Containstable to do it or is there a way to do it directly
with this query?
Thanks,
Tom
You have to use ContainsTable - here is my stab at the query - it is hard
for me to write this as I don't really know your schema - post it for a more
complete solution.
SELECT DatePosted,p.DateUpdated,Applied = Case when Email is NULL
then ' ' else '*' end,p.PositionID,JobTitle,JobDescription =
substring(JobDescription,1,200),Posted =
replace(convert(varchar,p.DatePosted,6), ' ',''),lastUpdated =
replace(convert(varchar,p.DateUpdated,6),' ',''), Company,Location FROM
ftsolutions..position p left outer join applicantPosition a on
(a.PositionID
= p.PositionID and Email = 'tscheiderich@.yahoo.com')
join ContainsTable(applicantPosition,jobDescription,'de veloper and sales')
as ft on a.PositionID=ft.[key]
where p.ClientID = 1234
and Contains(jobDescription,'developer and sales') order by ft.[rank] desc,
p.DateUpdated DESC
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:Ouxp8tTOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> I have the following query:
> SELECT Distinct DatePosted,p.DateUpdated,Applied = Case when Email is NULL
> then ' ' else '*' end,p.PositionID,JobTitle,JobDescription =
> substring(JobDescription,1,200),Posted =
> replace(convert(varchar,p.DatePosted,6), ' ',''),lastUpdated =
> replace(convert(varchar,p.DateUpdated,6),' ',''), Company,Location FROM
> ftsolutions..position p left outer join applicantPosition a on
(a.PositionID
> = p.PositionID and Email = 'tscheiderich@.yahoo.com') where p.ClientID =
1234
> and Contains(jobDescription,'developer and sales') order by p.DateUpdated
> DESC
> I want to change the option to order by keyword ranking.
> How would I do this?
> Do I have to use Containstable to do it or is there a way to do it
directly
> with this query?
> Thanks,
> Tom
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eBriEMUOFHA.2468@.tk2msftngp13.phx.gbl...
> You have to use ContainsTable - here is my stab at the query - it is hard
> for me to write this as I don't really know your schema - post it for a
> more
> complete solution.
That was exactly what I was looking for.
I wasn't sure how to use the containstable (a little confusing). I am still
confused as to why I need the same filter (developer and sales) in both the
Containstable and Contains verbs. But that is how I always see it.
Thanks,
Tom
> SELECT DatePosted,p.DateUpdated,Applied = Case when Email is NULL
> then ' ' else '*' end,p.PositionID,JobTitle,JobDescription =
> substring(JobDescription,1,200),Posted =
> replace(convert(varchar,p.DatePosted,6), ' ',''),lastUpdated =
> replace(convert(varchar,p.DateUpdated,6),' ',''), Company,Location FROM
> ftsolutions..position p left outer join applicantPosition a on
> (a.PositionID
> = p.PositionID and Email = 'tscheiderich@.yahoo.com')
> join ContainsTable(applicantPosition,jobDescription,'de veloper and sales')
> as ft on a.PositionID=ft.[key]
> where p.ClientID = 1234
> and Contains(jobDescription,'developer and sales') order by ft.[rank]
> desc,
> p.DateUpdated DESC
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:Ouxp8tTOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> (a.PositionID
> 1234
> directly
>
|||Ouch I am sorry, you don't need the second contains.
Try this
SELECT DatePosted,p.DateUpdated,Applied = Case when Email is NULL then ' '
else '*' end,p.PositionID,JobTitle,JobDescription =
substring(JobDescription,1,200),Posted =
replace(convert(varchar,p.DatePosted,6), ' ',''),lastUpdated =
replace(convert(varchar,p.DateUpdated,6),' ',''), Company,Location
FROM ftsolutions..position p left outer join applicantPosition a on
(a.PositionID= p.PositionID and Email = 'tscheiderich@.yahoo.com')
join ContainsTable(applicantPosition,jobDescription,'de veloper and sales')
as ft on a.PositionID=ft.[key] where p.ClientID = 1234 order by ft.[rank]
desc, p.DateUpdated DESC
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23fGemeUOFHA.1040@.TK2MSFTNGP12.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eBriEMUOFHA.2468@.tk2msftngp13.phx.gbl...
> That was exactly what I was looking for.
> I wasn't sure how to use the containstable (a little confusing). I am
> still confused as to why I need the same filter (developer and sales) in
> both the Containstable and Contains verbs. But that is how I always see
> it.
> Thanks,
> Tom
>
sql

ORDER BY clause

I would like to order my select statement depends on column3 value,
something like this:
SELECT * from table
ORDER BY column1,column2,case when column3=1 then column4,column5 else
column5,column4 end
Any idea?
Regards,SYou're almost there:
SELECT * from table
ORDER BY
column1
, column2
, case when column3=1 then column4 else column5 end
, case when column3=1 then column5 else column4 end
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"simon" <simon.zupan@.iware.si> wrote in message
news:YY2Je.1186$cE1.174161@.news.siol.net...
I would like to order my select statement depends on column3 value,
something like this:
SELECT * from table
ORDER BY column1,column2,case when column3=1 then column4,column5 else
column5,column4 end
Any idea?
Regards,S

Order By Case Confusion

I can't seem to figure out what is going on here... try this code. It should return 3 rows with no problem as is.

===========
DECLARE
@.SortOn varchar(20)

SET @.SortOn = 'dicount'

SELECT discounttype,
isNull(stor_id,'') stor_id,
isNull(lowqty,0) lowqty,
isNull(highqty,0) highqty,
discount
FROM pubs..discounts
ORDER BY CASE @.SortOn
WHEN 'discounttype' THEN discounttype
WHEN 'stor_id' THEN stor_id
WHEN 'lowqty' THEN lowqty
WHEN 'highqty' THEN highqty
WHEN 'dicount' THEN discount
END
===========

Now set @.SortOn = 'discounttype' and you get:
Server: Msg 8114, Level 16, State 5, Line 5
Error converting data type varchar to numeric.

?

Now for more confusion.. when you comment out lines 3,4,and 5 of the CASE statement (WHEN 'lowqty'...'highqty'...'dicount' lines) you once again get 3 rows returned without error.

Anyone able to shed some light on what is goin on?

[MESSAGE EDITED: misspelled discounttype.. sorry]The problem you are having is that you have numeric types mixed in with a varchar data type in your order by clause. SQL is expecting a numeric - look at data type precedence.|||Try this:

DECLARE @.SortOn varchar(20)

SET @.SortOn = 'dicount'

SELECT discounttype,
isNull(stor_id,'') stor_id,
isNull(lowqty,0) lowqty,
isNull(highqty,0) highqty,
discount
FROM pubs..discounts
ORDER BY
CASE @.SortOn
WHEN 'discounttype' THEN discounttype
WHEN 'stor_id' THEN CAST(stor_id AS VARCHAR)
WHEN 'lowqty' THEN CAST(lowqty AS VARCHAR)
WHEN 'highqty' THEN CAST(highqty AS VARCHAR)
WHEN 'dicount' THEN CAST(discount AS VARCHAR)
END|||The only problem with that code is that it will sort the numerics as varchars(ascii character comparision) and not as a true numeric. So values 10,9,12 would be sorted as 10,12,9 (ascending) and 9,12,10 (descending).|||The problem you are having is that you have numeric types mixed in with a varchar data type in your order by clause i've looked at that answer several times, and the error message too, and i still don't understand why it would throw an error

but then, the query's kind of weird too

why not stick the whatever-i-want-to-sort-on-this-week into an extra column in the result set and sort on that...SELECT discounttype
, isNull(stor_id,'') stor_id
, isNull(lowqty,0) lowqty
, isNull(highqty,0) highqty
, discount
, CASE @.SortOn
WHEN 'discounttype' THEN discounttype
WHEN 'stor_id' THEN stor_id
WHEN 'lowqty' THEN lowqty
WHEN 'highqty' THEN highqty
WHEN 'dicount' THEN discount
END as sortfield
FROM pubs..discounts
ORDER BY sortfieldmaybe that'll throw the same error, i dunno

rudy
http://rudy.ca/|||Originally posted by r937
maybe that'll throw the same error, i dunno

Nice thought but that errors as well.

I have solved my problem by seperating each datatype with an IF clause like so:

IF @.SortOn in (..) BEGIN -- varchars
SELECT ...
FROM ..
ORDER BY CASE @.SortOn
WHEN (varchars... etc..)
END
END ELSE BEGIN -- numerics
SELECT ...
FROM ..
ORDER BY CASE @.SortOn
WHEN (numerics... etc..)
END
END

Kinda messy but it works. Any thoughts to make it a bit more graceful would be appreciated.

Thanks for the replies!

Order By Case Cast Convert Error

I have created a SQL Stored Procedure that uses a Case statement to
determine the Order By. For one of the Case statements I am trying to
turn a Char field into Datetime in for the Order By, however I can not
get it to work. Can someone please take a look and my code below and
tell me what I am doing wrong. Thank you.

ORDER BY
CASE WHEN @.SortBy = 'dttm_stamp' THEN dttm_stamp End,
CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),
Event_Date1,101) as datetime) End,
CASE WHEN @.SortBy = 'FullName' THEN Emp_lastname + ', ' +
Emp_firstname End,
CASE WHEN @.SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +
Emp_firstname End DESC,
CASE WHEN @.SortBy = 'Emp_SSN' THEN Emp_SSN End<jguilford@.cybergroup.com> wrote in message
news:1105983724.986065.70140@.c13g2000cwb.googlegro ups.com...
>I have created a SQL Stored Procedure that uses a Case statement to
> determine the Order By. For one of the Case statements I am trying to
> turn a Char field into Datetime in for the Order By, however I can not
> get it to work. Can someone please take a look and my code below and
> tell me what I am doing wrong. Thank you.
> ORDER BY
> CASE WHEN @.SortBy = 'dttm_stamp' THEN dttm_stamp End,
> CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),
> Event_Date1,101) as datetime) End,
> CASE WHEN @.SortBy = 'FullName' THEN Emp_lastname + ', ' +
> Emp_firstname End,
> CASE WHEN @.SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +
> Emp_firstname End DESC,
> CASE WHEN @.SortBy = 'Emp_SSN' THEN Emp_SSN End

What does "can not get it to work" mean? Do you get errors, or do you get
unexpected results? The best idea would probably be to post CREATE TABLE and
INSERT statements to set up a test case which illustrates your problem.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||On 17 Jan 2005 09:42:05 -0800, jguilford@.cybergroup.com wrote:

>I have created a SQL Stored Procedure that uses a Case statement to
>determine the Order By. For one of the Case statements I am trying to
>turn a Char field into Datetime in for the Order By, however I can not
>get it to work. Can someone please take a look and my code below and
>tell me what I am doing wrong. Thank you.

Hi jquilford,

I guess that this is the line that's giving you trouble:

>CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),
>Event_Date1,101) as datetime) End,

You are converting the datetime variable to the american mm/dd/yyyy
format, which is not very well suited for sorting. Then you are converting
it back to datetime, running alll kinds of risks because this format is
ambiguous - it's easily misinterpreted as dd/mm/yyyy, causing either wrong
sorting or conversion errors.

If your intention is to strip the time part from the datetime column, so
you can order by the date part only, use

CASE WHEN @.SortBy = 'Event_Date1' THEN DATEADD(day, DATEDIFF(day,
'20000101', Event_Date1), '20000101')

Or, if you really want to do it by conversion to string and back, use the
safe yyyymmdd format:

CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(8),
Event_Date1,112) as datetime) End,

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Or, if you really want to do it by conversion to string and back, use the
> safe yyyymmdd format:
> CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(8),
> Event_Date1,112) as datetime) End,

Or simply say:

CASE WHEN @.SortBy = 'Event_Date1'
THEN CONVERT(char(8), Event_Date1,112)
End,

Then again, we have no idea jguildford mean "I can not get it to work".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 17 Jan 2005 22:40:11 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> Or, if you really want to do it by conversion to string and back, use the
>> safe yyyymmdd format:
>>
>> CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(8),
>> Event_Date1,112) as datetime) End,
>Or simply say:
> CASE WHEN @.SortBy = 'Event_Date1'
> THEN CONVERT(char(8), Event_Date1,112)
> End,

Hi Erland,

Of course - no need to change it back to datetime in this case. Thanks!

>Then again, we have no idea jguildford mean "I can not get it to work".

Maybe he (she?) will post with more details?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||1) why are you using a CASE expression in an ORDER BY? Is the
destruction of portable code one of your design goals?

2) The column used for a sort should appear in the output so a human
being can use it to search the list. Basic human factors, etc. Put it
in the SELECT list.

3) Why are you making a date into a string in the first place? You can
sort of temporal datatypes too. And why are you converting it to a
string that is not in temporal order?|||Let me try to better explain my problem. I have created a page in
asp.net that has a datagrid that pulls a few fields form a SQL Server
table. Also on this page there is a dropdown box that allows you to
pick the way the data is sorted. You can pick to sort it by the data
the record was created, name (both ascending and descending), social
security number and by the date of the event. Now here is my problem,
the Event_Date1 field is a 10 character field (00/00/0000) instead of a
datetime field, it was set up like this a long time ago and can not be
changed. Because it is a character field when you use it to sort the
data it is sorted by month then day then year instead of year then
month then day. I tried to use a Cast and Convert statement to change
it to a datetime so it would sort correctly but now when I try to sort
by Event_Date1 I receive this error: "The conversion of a char data
type to a datetime data type resulted in an out-of-range datetime
value." I have included all of the code in my stored procedure below.
I hop this clears up some of the confusion. Let me know if there is
anything else I did not clear up. Thank you.

CREATE PROCEDURE spShowArchives
@.SecurityID int,
@.SortBy varchar(50)
AS
SELECT ID, Emp_lastname + ', ' + Emp_firstname as FullName, Emp_SSN,
Event_Date1, Injury_Illness_Type, Jurisdiction, Injury_Cause_Desc,
SISCO_claim, dttm_stamp FROM omni_table
WHERE security_id = @.SecurityID
AND omni_table.deleted_flag = 0
AND (SISCO_claim <> '' or SISCO_claim <> null or SISCO_claim <>
'Submit')
ORDER BY
CASE WHEN @.SortBy = 'dttm_stamp' THEN dttm_stamp End,
CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),
Event_Date1,101) as datetime) End,
CASE WHEN @.SortBy = 'FullName' THEN Emp_lastname + ', ' +
Emp_firstname End,
CASE WHEN @.SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +
Emp_firstname End DESC,
CASE WHEN @.SortBy = 'Emp_SSN' THEN Emp_SSN End
GO|||On 18 Jan 2005 11:53:03 -0800, jagguil4d wrote:

>Let me try to better explain my problem.
(snip)
> Now here is my problem,
>the Event_Date1 field is a 10 character field (00/00/0000) instead of a
>datetime field, it was set up like this a long time ago and can not be
>changed.

Hi jagguil4d / jguilford,

This is not correct. Of course it *can* be changed. You probably meant to
write that someone in your organization doesn't *want* it to be changed.
And that someone is most likely the person who's budget is impacted by the
cost of actually imprivong your system, but doesn't suffer from the
(eventually) much higher costs of numerous workarounds, bugfixes and error
recovery.

> Because it is a character field when you use it to sort the
>data it is sorted by month then day then year instead of year then
>month then day. I tried to use a Cast and Convert statement to change
>it to a datetime so it would sort correctly but now when I try to sort
>by Event_Date1 I receive this error: "The conversion of a char data
>type to a datetime data type resulted in an out-of-range datetime
>value."

You might try if you have more luck with
CASE WHEN @.SortBy = 'Event_Date1' THEN CONVERT(datetime, Event_Date1,
101) End,

If that fails as well, you have at least one row in your table with an
invalid date (and boy, are you lucky if it is indeed only one <g>). These
can be hard to find. A good starting point would be
SELECT KeyColumn, Event_Date1
FROM omni_table
WHERE ISDATE(Event_Date1) = 0

If the above returns no rows, but you get errors converting Event_Date1 to
datetime, then you probably have rows with a date in DD/MM/YYYY format, or
some other date format. The following will hopefully catch most of these
buggers:
SELECT KeyColumn, Event_Date1
FROM omni_table
WHERE Event_Date1 >= '13'
OR Event_Date1 NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'

Good luck! (You'll need it...)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||jagguil4d (jguilford@.cybergroup.com) writes:
> Now here is my problem, the Event_Date1 field is a 10 character field
> (00/00/0000) instead of a datetime field, it was set up like this a long
> time ago and can not be changed.

Of course it can! I hear this lame excuse every time, but seriously,
yes it can be changed. Just why would it be left unchanged?

Anyway, as Hugo points out you have garabge in this column, so if
you insist on that you don't want to change it, do this:

CASE WHEN @.SortBy = 'Event_Date1' THEN
substring(Event_Date1, 7, 4) + substring(Event_Date1, 1, 2) +
substring(Event_Date1, 4, 2)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||--CELKO-- (jcelko212@.earthlink.net) writes:
> 1) why are you using a CASE expression in an ORDER BY? Is the
> destruction of portable code one of your design goals?

Because he more cares about serving his users than paying sacrifice
to the Holy Church of Portability.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> he more cares about serving his users than paying sacrifice to the
Holy Church of Portability. <<

When you can get portability for no extra cost, there is no sacrifice
and considerable gains in maintaining the code over the life of the
system. Would really use getdate() instead of CURRENT_TIMESTAMP to
save a few keystrokes? Or ISNULL() instead of COALESCE()?|||--CELKO-- (jcelko212@.earthlink.net) writes:
> When you can get portability for no extra cost, there is no sacrifice
> and considerable gains in maintaining the code over the life of the
> system. Would really use getdate() instead of CURRENT_TIMESTAMP to
> save a few keystrokes? Or ISNULL() instead of COALESCE()?

In this case you questioned the use of CASE in ORDER BY, which
jguilford had added to offer desired functionailty to his application.
So there is a cost to be portable here.

By the way, there is a situations where isnull() works, but not
coalesce().

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

ORDER BY CASe

I am creating a stored procedure to return a sorted list
Currently like this
DECLARE @.SORT tinyint
SET @.SORT = 1 --could be anything passed in
SELECT * FROM Table
ORDER BY
CASE
WHEN 1 THEN Col1
WHEN 2 THEN Col2
...etc
Now i would like to add 'ASC' or 'DESC' like follows
CASE
WHEN 1 THEN Col1 ASC
WHEN 2 THEN Col1 DESC
...etc
But this return an error 'Incorrect syntax near the keyword 'ASC'.'
IS there a way to make this work?
Yes, but the syntax is a bit weird:
create table #a(b char(1), c char(1))
insert #a values ('a', 'z')
insert #a values ('b', 'y')
insert #a values ('c', 'x')
declare @.orderby int
set @.orderby = 1
SELECT *
FROM #a
ORDER BY
CASE WHEN @.Orderby = 1 THEN b end asc,
CASE WHEN @.Orderby = 2 THEN b end desc,
CASE WHEN @.Orderby = 3 THEN c end asc,
CASE WHEN @.Orderby = 4 THEN c end desc
"GrantMagic" <grant@.magicalia.com> wrote in message
news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> I am creating a stored procedure to return a sorted list
> Currently like this
> DECLARE @.SORT tinyint
> SET @.SORT = 1 --could be anything passed in
> SELECT * FROM Table
> ORDER BY
> CASE
> WHEN 1 THEN Col1
> WHEN 2 THEN Col2
> ...etc
> Now i would like to add 'ASC' or 'DESC' like follows
> CASE
> WHEN 1 THEN Col1 ASC
> WHEN 2 THEN Col1 DESC
> ...etc
> But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> IS there a way to make this work?
>
|||Brilliant, that did the job perfectly!!
Thank You!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OJZDGddkEHA.3648@.TK2MSFTNGP09.phx.gbl...
> Yes, but the syntax is a bit weird:
>
> create table #a(b char(1), c char(1))
> insert #a values ('a', 'z')
> insert #a values ('b', 'y')
> insert #a values ('c', 'x')
> declare @.orderby int
> set @.orderby = 1
> SELECT *
> FROM #a
> ORDER BY
> CASE WHEN @.Orderby = 1 THEN b end asc,
> CASE WHEN @.Orderby = 2 THEN b end desc,
> CASE WHEN @.Orderby = 3 THEN c end asc,
> CASE WHEN @.Orderby = 4 THEN c end desc
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
>

ORDER BY CASE

Here is my ORDER BY Clause, and based on the @.Option parameter, I do
different order by. This works fine since the order is set from just one
column.
ORDER BY CASE @.Option
WHEN '1' THEN IsApproved ASC
WHEN '2' THEN CompletedDate
WHEN '3' THEN Priority
END
The problem is that as I add more columns to do order by, it errors (like
the following).
ORDER BY CASE 1
WHEN '1' THEN IsApproved ASC, CompletedDate DESC, ProjectID DESC,
Priority DESC, IsApproved DESC
WHEN '2' THEN CompletedDate ASC, ProjectID DESC
WHEN '3' THEN Priority ASC, Priority DESC
END
It seems you cannot have more than one columns if you use case statement in
the order by clause. then what is the purpose of case if you can just do
sort by just one column?
Additionaly, can this be done other than dynamic query (and other than bunch
of if else statement)?
Thanks all
JOrdering by CASE using multiple columns is not, unfortunately, very
straightforward. You have to do something like:
ORDER BY
CASE @.Option
WHEN 1 THEN IsApproved
WHEN 2 THEN CompletedDate
WHEN 3 THEN Priority
END ASC,
CASE @.Option
WHEN 1 THEN CompletedDate
WHEN 2 THEN ProjectId,
WHEN 3 THEN Priority
END DESC,
..
This may or may not be better than using dynamic SQL -- it really depends on
just how dynamic/complex the conditions are.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Justin" <jyang@.ioutsource.info> wrote in message
news:%23lx4Z1GaGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Here is my ORDER BY Clause, and based on the @.Option parameter, I do
> different order by. This works fine since the order is set from just one
> column.
> ORDER BY CASE @.Option
> WHEN '1' THEN IsApproved ASC
> WHEN '2' THEN CompletedDate
> WHEN '3' THEN Priority
> END
> The problem is that as I add more columns to do order by, it errors (like
> the following).
> ORDER BY CASE 1
> WHEN '1' THEN IsApproved ASC, CompletedDate DESC, ProjectID DESC,
> Priority DESC, IsApproved DESC
> WHEN '2' THEN CompletedDate ASC, ProjectID DESC
> WHEN '3' THEN Priority ASC, Priority DESC
> END
> It seems you cannot have more than one columns if you use case statement
> in the order by clause. then what is the purpose of case if you can just
> do sort by just one column?
> Additionaly, can this be done other than dynamic query (and other than
> bunch of if else statement)?
> Thanks all
> J
>|||I believe that Case returns a value, not a clause, so your case function is
treated like a single column and cannot be an order by clause in itself. In
this case, you could try using several case statements, although there may
be a better way.
For Case = 3 you have priority listed twice, in two different orders. I
assume this is a typo. Basically, you need a case for each column, not each
sort order.
Try this approach. It is really tedious, but should work.
ORDER BY
CASE
WHEN @.Option = '1' THEN IsApproved ASC
WHEN @.Option = '2' THEN CompletedDate ASC
WHEN @.Option = '3' THEN Priority ASC
END
,CASE
WHEN @.Option = '1' THEN CompletedDate DESC
WHEN @.Option = '2' THEN ProjectID DESC
WHEN @.Option = '3' THEN 1
END
,CASE
WHEN @.Option = '1' THEN ProjectID DESC
WHEN @.Option = '2' THEN 1
WHEN @.Option = '3' THEN 1
END
,CASE
WHEN @.Option = '1' THEN Priority DESC
WHEN @.Option = '2' THEN 1
WHEN @.Option = '3' THEN 1
END
,CASE
WHEN @.Option = '1' THEN IsApproved DESC
WHEN @.Option = '2' THEN 1
WHEN @.Option = '3' THEN 1
END
"Justin" <jyang@.ioutsource.info> wrote in message
news:%23lx4Z1GaGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Here is my ORDER BY Clause, and based on the @.Option parameter, I do
> different order by. This works fine since the order is set from just one
> column.
> ORDER BY CASE @.Option
> WHEN '1' THEN IsApproved ASC
> WHEN '2' THEN CompletedDate
> WHEN '3' THEN Priority
> END
> The problem is that as I add more columns to do order by, it errors (like
> the following).
> ORDER BY CASE 1
> WHEN '1' THEN IsApproved ASC, CompletedDate DESC, ProjectID DESC,
> Priority DESC, IsApproved DESC
> WHEN '2' THEN CompletedDate ASC, ProjectID DESC
> WHEN '3' THEN Priority ASC, Priority DESC
> END
> It seems you cannot have more than one columns if you use case statement
in
> the order by clause. then what is the purpose of case if you can just do
> sort by just one column?
> Additionaly, can this be done other than dynamic query (and other than
bunch
> of if else statement)?
> Thanks all
> J
>|||I missed on thing here...
As Adam has in his post, the ASC, DESC has to come after the END of the case
function.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eHtPc9GaGHA.440@.TK2MSFTNGP05.phx.gbl...
> I believe that Case returns a value, not a clause, so your case function
is
> treated like a single column and cannot be an order by clause in itself.
In
> this case, you could try using several case statements, although there may
> be a better way.
> For Case = 3 you have priority listed twice, in two different orders. I
> assume this is a typo. Basically, you need a case for each column, not
each
> sort order.
> Try this approach. It is really tedious, but should work.
> ORDER BY
> CASE
> WHEN @.Option = '1' THEN IsApproved
> WHEN @.Option = '2' THEN CompletedDate
> WHEN @.Option = '3' THEN Priority
> END ASC
> ,CASE
> WHEN @.Option = '1' THEN CompletedDate
> WHEN @.Option = '2' THEN ProjectID
> WHEN @.Option = '3' THEN 1
> END DESC
> ,CASE
> WHEN @.Option = '1' THEN ProjectID
> WHEN @.Option = '2' THEN 1
> WHEN @.Option = '3' THEN 1
> END DESC
> ,CASE
> WHEN @.Option = '1' THEN Priority
> WHEN @.Option = '2' THEN 1
> WHEN @.Option = '3' THEN 1
> END DESC
> ,CASE
> WHEN @.Option = '1' THEN IsApproved
> WHEN @.Option = '2' THEN 1
> WHEN @.Option = '3' THEN 1
> END DESC
> "Justin" <jyang@.ioutsource.info> wrote in message
> news:%23lx4Z1GaGHA.1220@.TK2MSFTNGP02.phx.gbl...
one
(like
> in
do
> bunch
>|||you can try it this way. I can understand its painful but I don't see any
other option
ORDER BY CASE @.option
WHEN '1' THEN IsApproved
WHEN '2' THEN CompletedDate
WHEN '3' THEN Priority
END ASC,
CASE @.option
WHEN '1' THEN CompletedDate
WHEN '2' THEN ProjectID
WHEN '3' THEN Priority
END DESC
by the way
ORDER BY CASE @.Option
WHEN '1' THEN IsApproved ASC <-- ASC here will not work to my knowledge
WHEN '2' THEN CompletedDate
WHEN '3' THEN Priority
END|||Justin,
I'd suggest this approach:
ORDER BY
CASE WHEN @.Option = 1 THEN IsApproved END ASC,
CASE WHEN @.Option = 1 THEN CompletedDate END DESC,
CASE WHEN @.Option = 1 THEN ProjectID END DESC,
CASE WHEN @.Option = 2 THEN CompletedDate END ASC,
CASE WHEN @.Option = 2 THEN ProjectID END DESC,
CASE WHEN @.Option = 3 THEN Priority END ASC,
CASE WHEN @.Option = 3 THEN CompletedDate END DESC,
CASE WHEN @.Option = 3 THEN CompletedDate END DESC
It's relatively manageable, and it also avoids the type conversion
errors you can get when you have more than one CASE alternative.
Steve Kass
Drew University
Justin wrote:

>Here is my ORDER BY Clause, and based on the @.Option parameter, I do
>different order by. This works fine since the order is set from just one
>column.
>ORDER BY CASE @.Option
> WHEN '1' THEN IsApproved ASC
> WHEN '2' THEN CompletedDate
> WHEN '3' THEN Priority
> END
>The problem is that as I add more columns to do order by, it errors (like
>the following).
>ORDER BY CASE 1
> WHEN '1' THEN IsApproved ASC, CompletedDate DESC, ProjectID DESC,
>Priority DESC, IsApproved DESC
> WHEN '2' THEN CompletedDate ASC, ProjectID DESC
> WHEN '3' THEN Priority ASC, Priority DESC
> END
>It seems you cannot have more than one columns if you use case statement in
>the order by clause. then what is the purpose of case if you can just do
>sort by just one column?
>Additionaly, can this be done other than dynamic query (and other than bunc
h
>of if else statement)?
>Thanks all
>J
>
>

order by case

I'm hoping there's an easy solution to this. When ordering with a case
statement this works:
select * from blah
order by case when @.sort = 1 then customer_no
else lastname end
But if I want the case statement to sort on multiple columns:
select * from blah
order by case when @.sort = 1 then customer_no
else lastname, firstname, customer_no end -- problem
It doesn't like the lastname, firstname, customer_no part.
Thanks for any suggestions.SG,
Try (as example(:
DECLARE @.SORT TINYINT
SET @.SORT = 0
SELECT *
FROM AUTHORS
ORDER BY CASE WHEN @.SORT = 1 THEN AU_LNAME ELSE STATE END, CITY
HTH
Jerry
"sg" <sg@.noemail.com> wrote in message
news:uwdKHY4yFHA.3892@.TK2MSFTNGP12.phx.gbl...
> I'm hoping there's an easy solution to this. When ordering with a case
> statement this works:
> select * from blah
> order by case when @.sort = 1 then customer_no
> else lastname end
> But if I want the case statement to sort on multiple columns:
> select * from blah
> order by case when @.sort = 1 then customer_no
> else lastname, firstname, customer_no end -- problem
> It doesn't like the lastname, firstname, customer_no part.
> Thanks for any suggestions.
>|||CASE is an expression and can only return one value. Using data type
precedence, all possible outcomes must either be or be convertible to a
common data type. You cannot use CASE for logic flow, e.g. to replace IF /
ELSE.
So, you need to break this out a little differently.
ORDER BY CASE @.sort
WHEN 1 THEN customer_no
ELSE lastname END,
CASE @.sort WHEN 1 THEN NULL ELSE firstname END
Though unless customer_no is not a key, this will probably work equally
well:
ORDER BY CASE @.sort
WHEN 1 THEN customer_no END,
lastname, firstname
If customer_no is sorted on first, then the rest of the order by clause
won't have any effect, because every customer_no is unique. If the first
ORDER BY is NULL, then the lastname, firstname sort order is obeyed.
In either case, adding customer_no to the end of the order by is only going
to come into play in the rare case that users have identical first/last
names, in which case, does it really matter which customer_no is displayed
first?
For some more information on this topic, see http://www.aspfaq.com/2501
"sg" <sg@.noemail.com> wrote in message
news:uwdKHY4yFHA.3892@.TK2MSFTNGP12.phx.gbl...
> I'm hoping there's an easy solution to this. When ordering with a case
> statement this works:
> select * from blah
> order by case when @.sort = 1 then customer_no
> else lastname end
> But if I want the case statement to sort on multiple columns:
> select * from blah
> order by case when @.sort = 1 then customer_no
> else lastname, firstname, customer_no end -- problem
> It doesn't like the lastname, firstname, customer_no part.
> Thanks for any suggestions.
>sql

ORDER BY CASe

I am creating a stored procedure to return a sorted list
Currently like this
DECLARE @.SORT tinyint
SET @.SORT = 1 --could be anything passed in
SELECT * FROM Table
ORDER BY
CASE
WHEN 1 THEN Col1
WHEN 2 THEN Col2
...etc
Now i would like to add 'ASC' or 'DESC' like follows
CASE
WHEN 1 THEN Col1 ASC
WHEN 2 THEN Col1 DESC
...etc
But this return an error 'Incorrect syntax near the keyword 'ASC'.'
IS there a way to make this work?Yes, but the syntax is a bit weird:
create table #a(b char(1), c char(1))
insert #a values ('a', 'z')
insert #a values ('b', 'y')
insert #a values ('c', 'x')
declare @.orderby int
set @.orderby = 1
SELECT *
FROM #a
ORDER BY
CASE WHEN @.Orderby = 1 THEN b end asc,
CASE WHEN @.Orderby = 2 THEN b end desc,
CASE WHEN @.Orderby = 3 THEN c end asc,
CASE WHEN @.Orderby = 4 THEN c end desc
"GrantMagic" <grant@.magicalia.com> wrote in message
news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> I am creating a stored procedure to return a sorted list
> Currently like this
> DECLARE @.SORT tinyint
> SET @.SORT = 1 --could be anything passed in
> SELECT * FROM Table
> ORDER BY
> CASE
> WHEN 1 THEN Col1
> WHEN 2 THEN Col2
> ...etc
> Now i would like to add 'ASC' or 'DESC' like follows
> CASE
> WHEN 1 THEN Col1 ASC
> WHEN 2 THEN Col1 DESC
> ...etc
> But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> IS there a way to make this work?
>|||Brilliant, that did the job perfectly!!
Thank You!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OJZDGddkEHA.3648@.TK2MSFTNGP09.phx.gbl...
> Yes, but the syntax is a bit weird:
>
> create table #a(b char(1), c char(1))
> insert #a values ('a', 'z')
> insert #a values ('b', 'y')
> insert #a values ('c', 'x')
> declare @.orderby int
> set @.orderby = 1
> SELECT *
> FROM #a
> ORDER BY
> CASE WHEN @.Orderby = 1 THEN b end asc,
> CASE WHEN @.Orderby = 2 THEN b end desc,
> CASE WHEN @.Orderby = 3 THEN c end asc,
> CASE WHEN @.Orderby = 4 THEN c end desc
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> > I am creating a stored procedure to return a sorted list
> >
> > Currently like this
> >
> > DECLARE @.SORT tinyint
> > SET @.SORT = 1 --could be anything passed in
> >
> > SELECT * FROM Table
> > ORDER BY
> >
> > CASE
> > WHEN 1 THEN Col1
> > WHEN 2 THEN Col2
> > ...etc
> >
> > Now i would like to add 'ASC' or 'DESC' like follows
> >
> > CASE
> > WHEN 1 THEN Col1 ASC
> > WHEN 2 THEN Col1 DESC
> > ...etc
> >
> > But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> >
> > IS there a way to make this work?
> >
> >
>

ORDER BY CASE

this is my query=

"SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' AND
type='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end"

My SortKey can be NULL. Here's the output I am getting:
(the || is to denote sortkey column)

37542 Tape 1 ||
37544 Tape 2 ||
37819 Symphony1 ||
37820 Symphony2 ||
37821 Symphony3 ||
37828 Symphony ||
60962 Test ||
61570 New Test Record |Africa|
61572 Test 3 |Africa 1|
63186 Music for Strings |Brazil|

I want use Sortkey when it is not null. desired output:

61570 New Test Record |Africa|
61572 Test 3 |Africa 1|
63186 Music for Strings |Brazil|
37542 Tape 1 ||
37544 Tape 2 ||
37819 Symphony1 ||
37820 Symphony2 ||
37821 Symphony3 ||
37828 Symphony ||
60962 Test ||Sharif Islam wrote:

Quote:

Originally Posted by

this is my query=
>
"SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' AND
type='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end"

ok I think figured it out, i needed a desc at the end.

SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' AND
type='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end desc"

let me know if there's a better way to do this.

Quote:

Originally Posted by

>
My SortKey can be NULL. Here's the output I am getting:
(the || is to denote sortkey column)
>
37542 Tape 1 ||
37544 Tape 2 ||
37819 Symphony1 ||
37820 Symphony2 ||
37821 Symphony3 ||
37828 Symphony ||
60962 Test ||
61570 New Test Record |Africa|
61572 Test 3 |Africa 1|
63186 Music for Strings |Brazil|
>
I want use Sortkey when it is not null. desired output:
>
61570 New Test Record |Africa|
61572 Test 3 |Africa 1|
63186 Music for Strings |Brazil|
37542 Tape 1 ||
37544 Tape 2 ||
37819 Symphony1 ||
37820 Symphony2 ||
37821 Symphony3 ||
37828 Symphony ||
60962 Test ||

|||Sharif Islam wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>"SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
>(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' AND
>type='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end"

Quote:

Originally Posted by

Quote:

Originally Posted by

>I want use Sortkey when it is not null. desired output:

Quote:

Originally Posted by

ok I think figured it out, i needed a desc at the end.

Quote:

Originally Posted by

SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' AND
type='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end desc"

Quote:

Originally Posted by

let me know if there's a better way to do this.

This ensures that the query will return rows with non-null SortKey
first, rows with null SortKey second.

It does /not/ ensure that the query will return rows with SortKey
"Africa" first, rows with SortKey "Brazil" second. It happened
to work that way this time, but there are no guarantees that it
will work that way every time. To get that guarantee, do this:

order by
CASE WHEN SortKey is not null then 1 else 2 end,
coalesce(SortKey,'')|||A quick lesson in good software engineering and SQL: Put the sort key
into a column in your result SELECT list and give it a name.

1) Good SQL: Using an expression in an ORDER BY clause is a
proprietary "feature" in SQL Server and not Standard SQL. The ORDER BY
caluse is alawyas part of a cursor, not a SELECT statement, since
DSELECT produces a table which has no order by definition.

2) Good S.E.:Always show what you used to sort a result set to the next
tier of the architecture. They might need to use it. Hey, the final
user might fidn data easier to find on his display if it was there!|||>A quick lesson in good software engineering and SQL: Put the sort key

Quote:

Originally Posted by

into a column in your result SELECT list and give it a name.
>
1) Good SQL: Using an expression in an ORDER BY clause is a
proprietary "feature" in SQL Server and not Standard SQL. The ORDER BY
caluse is alawyas part of a cursor, not a SELECT statement, since
DSELECT produces a table which has no order by definition.
>
2) Good S.E.:Always show what you used to sort a result set to the next
tier of the architecture. They might need to use it. Hey, the final
user might fidn data easier to find on his display if it was there!
>

That is a mistake class room coders often make; they don't take into
consideration the extra resource the extra columns require when say
bandwidth may be a limiting factor to scalability.

Why pass something you don't need?

This is a MICROSOFT SQL SERVER news group (you may do well to remember that)
and NOT a STANDARD SQL one - as far as I know no such group exists which
suggests there is not much call for it.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.netwrote in message
news:1166906010.557020.127250@.79g2000cws.googlegro ups.com...

Quote:

Originally Posted by

>A quick lesson in good software engineering and SQL: Put the sort key
into a column in your result SELECT list and give it a name.
>
1) Good SQL: Using an expression in an ORDER BY clause is a
proprietary "feature" in SQL Server and not Standard SQL. The ORDER BY
caluse is alawyas part of a cursor, not a SELECT statement, since
DSELECT produces a table which has no order by definition.
>
2) Good S.E.:Always show what you used to sort a result set to the next
tier of the architecture. They might need to use it. Hey, the final
user might fidn data easier to find on his display if it was there!
>

|||Joe,

How do you answer the question:
Give me all employees of the 5 best paid managers?
This query is very easy to write with a nested TOP/ORDER BY.

SELECT * FROM EMP
WHERE MGRID IN (SELECT TOP 5 EMPID FROM MGR
ORDER BY SALARY DESC)

It gets really nasty without. Matter of fact I can't think of a way
without cheating (e.g. using ROW_NUMBER() to sneak in the ORDER BY
through the back door).

Are you saying SQL shouldn't be able to answer such queries without
escaping into the application? I doubt that's what Codd had in mind...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html|||>Are you saying SQL shouldn't be able to answer such queries without escaping into the application? <<

I would use the "ROW_NUMBER() OVER ()" that you guys at IBM put into
DB2 !!|||--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>Are you saying SQL shouldn't be able to answer such queries without escaping into the application? <<

>
I would use the "ROW_NUMBER() OVER ()" that you guys at IBM put into
DB2 !!

... and the SQL Standard...

Well, that's what I referred to as cheating, since it requires usage
ROW_NUMBER() OVER ( _ORDER_BY_ ...). So you avoid one ORDER BY by
adding another. How come one is bad while the other isn't ?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html|||>So you avoid one ORDER BY by adding another. How come one is bad while the other isn't ? <<

Language Standards. And an underlying definition based on von Nueman's
definition of ordinal numbers in Set Theory. DUH! :)|||--CELKO-- wrote:

Quote:

Originally Posted by

Language Standards.

Winds of change are blowing and they wisper "FETCH FIRST n ROWS". Every
SQL based product has some sort of mechanism to get the job done.
Doesn't that just scream need? Whether it's TOP, ROWNUM, LIMIT OFFSET...
All of them address the need to cut pieces out of a set based on order.

Quote:

Originally Posted by

>And an underlying definition based on von Nueman's
definition of ordinal numbers in Set Theory. DUH! :)

Living in the past, are we...? The result of a refusal to acknowledge a
need is that the standard got fractured at this point.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html|||On Sat, 23 Dec 2006 18:17:54 -0500, Serge Rielau <srielau@.ca.ibm.com>
wrote:

Quote:

Originally Posted by

>Give me all employees of the 5 best paid managers?
>This query is very easy to write with a nested TOP/ORDER BY.
>
>SELECT * FROM EMP
WHERE MGRID IN (SELECT TOP 5 EMPID FROM MGR
ORDER BY SALARY DESC)
>
>It gets really nasty without. Matter of fact I can't think of a way
>without cheating (e.g. using ROW_NUMBER() to sneak in the ORDER BY
>through the back door).

If you allow WITH DUPS instead of arbitrarily dropping one:

SELECT *
FROM EMP
WHERE MGRID IN
(SELECT EMPID FROM MGR as M1
WHERE (SELECT count(*) from MGR as M2
WHERE M1.SALARY <= M2.SALARY) <= 5)

Roy Harvey
Beacon Falls, CT

Order by & case

I am trying to use a case statment to indicate sort order. Everything worked fine untill I added "desc".

create table #tmp(f1 varchar(25), dtObs datetime, dtCnt datetime)
insert into #tmp values('ABC','01-Jan-2003','02-Jan-2003')
insert into #tmp values('BCD','01-Jan-2003','03-Jan-2003')
insert into #tmp values('CDE','01-Jan-2003','04-Jan-2003')
insert into #tmp values('DEF','02-Jan-2003','03-Jan-2003')
insert into #tmp values('EFG','02-Jan-2003','04-Jan-2003')
insert into #tmp values('FGH','02-Jan-2003','05-Jan-2003')

declare @.Order tinyint
set @.Order = 1
select *
from #tmp
order by case @.Order when 1 then 'dtObs'
when 2 then 'dtCnt'
when 3 then 'dtObs desc'
when 4 then 'dtCnt desc'
else null end

any suggestion on how to do this?select *
from #tmp
order by case @.Order when 1 then 'dtObs'
when 2 then 'dtCnt'
else null end
,case @.Order when 3 then 'dtObs'
when 4 then 'dtCnt'
else null end DESC|||Thanks!|||Pleasure to help you.|||I am using a Case statement within my Order By clause, but I have columns that I want to use that were created in my Select clause as expressions. I cannot seem to use those columns in my Case statement. Is there a way to do this?? Thank you very much for your help!!!sql