June 30, 2008 at 2:01 pm
I need to send the 'ORDER BY' expression in a variable. Basically here's what I'm trying to do, this works:
SELECT zipcode,city,state
ORDER BY
CASE @SortExp
WHEN 'zipcode' THEN ZipCode
WHEN 'state' THEN State
END
But put in DISTINCT and it stops working, IE this does Not work:
SELECT DISTINCT zipcode,city,state
ORDER BY
CASE @SortExp
WHEN 'zipcode' THEN ZipCode
WHEN 'state' THEN State
END
How can I do this?
June 30, 2008 at 2:12 pm
I'm sure someone will have a better idea than this, but you can get around it by adding the exact content of your ORDER BY to the select:
SELECT DISTINCT zipcode
,city
,state
,CASE @SortExp
WHEN 'zipcode' THEN ZipCode
WHEN 'state' THEN State END
.... FROM/WHERE clause....
ORDER BY CASE @SortExp
WHEN 'zipcode' THEN ZipCode
WHEN 'state' THEN State END
Hope this helps!
Chad
June 30, 2008 at 3:07 pm
got to be the distinct clauses way of handling things right?
I wonder if instead of a distinct at the head of the statement a group by at the foot would work for you?
I tried it out on one of the databases I work with and it seems ok.
Only thing to remember is that if its not grouped by then its an aggregate and if you are ordering by an aggregate column then you have to include the aggregate function in the order by
June 30, 2008 at 3:32 pm
"But put in DISTINCT and it stops working" - actually it gives an error "Server: Msg 145, Level 15, State 1, Line 4
ORDER BY items must appear in the select list if SELECT DISTINCT is specified." under both SQL Server 2000 and 2008.
One workaround is to nest the select:
declare @sortvarchar(255)
set@sort = 'city'
select*
from (select distinct City
,country
,PostalCode
fromNorthwind.dbo.Customers
) as X
order by CASE @sort
when 'city' then city
else country
end
SQL = Scarcely Qualifies as a Language
July 1, 2008 at 5:15 am
Thanks Carl!! That code worked great.
What about having another variable @SortDir, which could be ASC or DESC?
Using the same method used for the Sort Expression (CASE) doesn't seem to work..
July 1, 2008 at 7:19 am
Orblin McLoon (7/1/2008)
Thanks Carl!! That code worked great.What about having another variable @SortDir, which could be ASC or DESC?
Using the same method used for the Sort Expression (CASE) doesn't seem to work..
You need to use a nested case to do it
SELECT zipcode,city,state
FROM addresstable
GROUP BY zipcode,city,state
ORDER BY
case @sortDir
when 'D' then
CASE @SortExp
WHEN 'zipcode' THEN ZipCode
WHEN 'state' THEN State
END DESC
else
CASE @SortExp
WHEN 'zipcode' THEN ZipCode
WHEN 'state' THEN State
END ASC
end
July 1, 2008 at 7:52 am
john.henderson (7/1/2008)
Orblin McLoon (7/1/2008)
Thanks Carl!! That code worked great.What about having another variable @SortDir, which could be ASC or DESC?
Using the same method used for the Sort Expression (CASE) doesn't seem to work..
You need to use a nested case to do it
SELECT zipcode,city,state
FROM addresstable
GROUP BY zipcode,city,state
ORDER BY
case @sortDir
when 'D' then
CASE @SortExp
WHEN 'zipcode' THEN ZipCode
WHEN 'state' THEN State
END DESC
else
CASE @SortExp
WHEN 'zipcode' THEN ZipCode
WHEN 'state' THEN State
END ASC
end
This didn't work - I think its trying to put the results of both @SortExp and ASC/DESC into @SortDir
July 1, 2008 at 10:01 am
Please see "How do I use a variable in an ORDER BY clause?" at
http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html
SQL = Scarcely Qualifies as a Language
July 1, 2008 at 3:07 pm
appologies all - I should have looked up my code last night rather than try to remember...
here is the order by clause for an address table that does work
ORDER BY
case @sortDir
when 'A' then
CASE @SortExp
WHEN 'zipcode' THEN zipcode
WHEN 'state' THEN state
END
end asc,
case @sortDir
when 'D' then
CASE @SortExp
WHEN 'zipcode' THEN zipcode
WHEN 'state' THEN state
END
end desc
July 3, 2008 at 1:10 pm
john.henderson (7/1/2008)
appologies all - I should have looked up my code last night rather than try to remember...here is the order by clause for an address table that does work
ORDER BY
case @sortDir
when 'A' then
CASE @SortExp
WHEN 'zipcode' THEN zipcode
WHEN 'state' THEN state
END
end asc,
case @sortDir
when 'D' then
CASE @SortExp
WHEN 'zipcode' THEN zipcode
WHEN 'state' THEN state
END
end desc
Thanks, works great! I'm almost there but now I'm having another bizarre problem. Here's a part of my code:
CASE @SortExp
WHEN 'city' THEN city
WHEN 'areacode' THEN Areacode
WHEN 'state' THEN state
ELSE zipcode
END
ASC
This works perfectly for 'areacode' and 'zipcode', but for state and city, I get this error:
"Error converting data type varchar to numeric."
The city and state fields are varchar, whereas the two that work are numeric, but I don't see why this would matter because @SortExp is declared varchar(50).
Also what makes it weird - if I get rid of all that and use: ORDER BY city ASC
it sorts by city with no problem..
Any ideas?
July 3, 2008 at 1:30 pm
The CASE statement needs to be able to consistently return a single data type (so all of the "then's" are getting cast to a single datatype). In this case - it thinks the data type should be numeric, so it's trying to cast the varchar's to numeric.
The only way around that is to force the CAST (in this case - cast the numerics to VARCHAR, possibly padding the front with 0's so that you keep the numeric sorting), which unfortunately may slow things down quite a bit on the numeric columns (since you now have a function in the ORDER BY clause).
If you're not willing to take the perf hit - I'm thinking you're looking at dynamic SQL.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply