September 9, 2010 at 7:53 am
I have the rather complicated 'ORDER BY' clause below, and I can't find a way to get it to order by more than one column. Is this not possible using the CASE statement?
Thanks!
ORDER BY
CASE
WHEN @SortSeq = 'asc' AND @SortOrder = 1 THEN machineName
WHEN @SortSeq = 'asc' AND @SortOrder = 2 THEN partDisplayName
WHEN @SortSeq = 'asc' AND @SortOrder = 3 THEN circuitDisplayName
WHEN @SortSeq = 'asc' AND @SortOrder = 4 THEN sourceLastName
WHEN @SortSeq = 'asc' AND @SortOrder = 5 THEN D.circuitType
WHEN @SortSeq = 'asc' AND @SortOrder = 6 THEN CAST(E.compDatetc AS VARCHAR(50))
END ASC,
CASE
WHEN @SortSeq = 'desc' AND @SortOrder = 1 THEN machineName
WHEN @SortSeq = 'desc' AND @SortOrder = 2 THEN partDisplayName
WHEN @SortSeq = 'desc' AND @SortOrder = 3 THEN circuitDisplayName
WHEN @SortSeq = 'desc' AND @SortOrder = 4 THEN sourceLastName
WHEN @SortSeq = 'desc' AND @SortOrder = 5 THEN D.circuitType
WHEN @SortSeq = 'desc' AND @SortOrder = 6 THEN CAST(E.compDatetc AS VARCHAR(50))
END DESC
September 9, 2010 at 8:26 am
I found that the data types have to be the same when you are ordering by in a Case Statement. Can you provide the DDL and sample data so we can see what you have and what you are doing?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 9, 2010 at 9:25 am
If you are dealing with mixed types you can cast everything to sql_variant.
Here is a link that describes the technique:
http://www.norimek.com/blog/post/2008/04/Dynamic-Sort-Parameters-in-MS-SQL-Server-2005.aspx
September 9, 2010 at 10:47 am
It actually sounds like the OP wants both a primary and secondary sort for each sort order choice. If that is the case, there are two options:
1) Have the expression returned by the CASE statement include both columns (e.g., Column1 + '\' + Column2)
2) Have another set of CASE statements for the second column.
The first option requires both columns to be (CAST to) compatible types and may take some tweaking if the first column is variable width.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 9, 2010 at 2:02 pm
can't find a way to get it to order by more than one column. Is this not possible using the CASE statement?
Yes, but your existing code can only handle one sort column at a time.
You will need to add additional CASE(s) to sort by additional column(s).
Scott Pletcher, SQL Server MVP 2008-2010
September 10, 2010 at 7:49 am
Yes, I would like to be able to do this:
ORDER BY
CASE
WHEN @SortSeq = 'asc' AND @SortOrder = 1 THEN machineName, partDisplayName
WHEN @SortSeq = 'asc' AND @SortOrder = 2 THEN partDisplayName, machineName
WHEN @SortSeq = 'asc' AND @SortOrder = 3 THEN circuitDisplayName, D.circuitType
WHEN @SortSeq = 'asc' AND @SortOrder = 4 THEN sourceLastName, CAST(E.compDatetc AS VARCHAR(50))
WHEN @SortSeq = 'asc' AND @SortOrder = 5 THEN D.circuitType
WHEN @SortSeq = 'asc' AND @SortOrder = 6 THEN CAST(E.compDatetc AS VARCHAR(50))
END ASC,
CASE
WHEN @SortSeq = 'desc' AND @SortOrder = 1 THEN machineName , partDisplayName
WHEN @SortSeq = 'desc' AND @SortOrder = 2 THEN partDisplayName, machineName
WHEN @SortSeq = 'desc' AND @SortOrder = 3 THEN circuitDisplayName, D.circuitType
WHEN @SortSeq = 'desc' AND @SortOrder = 4 THEN sourceLastName, CAST(E.compDatetc AS VARCHAR(50))
WHEN @SortSeq = 'desc' AND @SortOrder = 5 THEN D.circuitType
WHEN @SortSeq = 'desc' AND @SortOrder = 6 THEN CAST(E.compDatetc AS VARCHAR(50))
END DESC
But it wont let me do that...
September 10, 2010 at 8:19 am
Something like this:
ORDER BY
CASE
WHEN @SortSeq = 'asc' AND @SortOrder = 1 THEN machineName
WHEN @SortSeq = 'asc' AND @SortOrder = 2 THEN partDisplayName
WHEN @SortSeq = 'asc' AND @SortOrder = 3 THEN circuitDisplayName
WHEN @SortSeq = 'asc' AND @SortOrder = 4 THEN sourceLastName
WHEN @SortSeq = 'asc' AND @SortOrder = 5 THEN D.circuitType
WHEN @SortSeq = 'asc' AND @SortOrder = 6 THEN CAST(E.compDatetc AS VARCHAR(50))
ELSE ''
END ASC,
CASE
WHEN @SortSeq = 'desc' AND @SortOrder = 1 THEN machineName
WHEN @SortSeq = 'desc' AND @SortOrder = 2 THEN partDisplayName
WHEN @SortSeq = 'desc' AND @SortOrder = 3 THEN circuitDisplayName
WHEN @SortSeq = 'desc' AND @SortOrder = 4 THEN sourceLastName
WHEN @SortSeq = 'desc' AND @SortOrder = 5 THEN D.circuitType
WHEN @SortSeq = 'desc' AND @SortOrder = 6 THEN CAST(E.compDatetc AS VARCHAR(50))
ELSE ''
END DESC,
ORDER BY
CASE
WHEN @SortSeq = 'asc' AND @SortOrder = 1 THEN partDisplayName
WHEN @SortSeq = 'asc' AND @SortOrder = 2 THEN machineName
WHEN @SortSeq = 'asc' AND @SortOrder = 3 THEN D.circuitType
WHEN @SortSeq = 'asc' AND @SortOrder = 4 THEN CAST(E.compDatetc AS VARCHAR(50))
ELSE ''
END ASC,
CASE
WHEN @SortSeq = 'desc' AND @SortOrder = 1 THEN partDisplayName
WHEN @SortSeq = 'desc' AND @SortOrder = 2 THEN machineName
WHEN @SortSeq = 'desc' AND @SortOrder = 3 THEN D.circuitType
WHEN @SortSeq = 'desc' AND @SortOrder = 4 THEN CAST(E.compDatetc AS VARCHAR(50))
ELSE ''
END DESC
Scott Pletcher, SQL Server MVP 2008-2010
September 10, 2010 at 11:00 am
Magy (9/10/2010)
Yes, I would like to be able to do this:
ORDER BY
CASE
WHEN @SortSeq = 'asc' AND @SortOrder = 1 THEN machineName, partDisplayName
WHEN @SortSeq = 'asc' AND @SortOrder = 2 THEN partDisplayName, machineName
WHEN @SortSeq = 'asc' AND @SortOrder = 3 THEN circuitDisplayName, D.circuitType
WHEN @SortSeq = 'asc' AND @SortOrder = 4 THEN sourceLastName, CAST(E.compDatetc AS VARCHAR(50))
WHEN @SortSeq = 'asc' AND @SortOrder = 5 THEN D.circuitType
WHEN @SortSeq = 'asc' AND @SortOrder = 6 THEN CAST(E.compDatetc AS VARCHAR(50))
END ASC,
CASE
WHEN @SortSeq = 'desc' AND @SortOrder = 1 THEN machineName , partDisplayName
WHEN @SortSeq = 'desc' AND @SortOrder = 2 THEN partDisplayName, machineName
WHEN @SortSeq = 'desc' AND @SortOrder = 3 THEN circuitDisplayName, D.circuitType
WHEN @SortSeq = 'desc' AND @SortOrder = 4 THEN sourceLastName, CAST(E.compDatetc AS VARCHAR(50))
WHEN @SortSeq = 'desc' AND @SortOrder = 5 THEN D.circuitType
WHEN @SortSeq = 'desc' AND @SortOrder = 6 THEN CAST(E.compDatetc AS VARCHAR(50))
END DESC
But it wont let me do that...
That is because a CASE statement can only return a single expression and you're trying to return TWO expressions. You can turn your two expressions into a single expression by concatenating them as I suggested earlier or by using additional CASE statements as I also suggested and Scott has demonstrated.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply