469,572 Members | 1,745 Online

# Is there a way to do ORDER BY with DESC inside a CASE statement?

Is there a way to do ORDER BY with DESC inside a CASE statement? That
is, given more than one choice for an ORDER BY based on a CASE
statement, but only one of the choices will also use DESC (others rely
on the default of ASC).

For example:

While this works:

WITH A(A, B) AS (VALUES (1,2), (2,1), (3,4)) SELECT A, B FROM A ORDER
BY CASE 1 WHEN 1 THEN A ELSE 2 END DESC

These do not work:

WITH A(A, B) AS (VALUES (1,2), (2,1), (3,4)) SELECT A, B FROM A ORDER
BY CASE 1 WHEN 1 THEN A DESC ELSE 2 END

WITH A(A, B) AS (VALUES (1,2), (2,1), (3,4)) SELECT A, B FROM A ORDER
BY CASE 1 WHEN 1 THEN A ELSE 2 END CASE 1 WHEN 1 THEN DESC END

If i only wanted DESC when A is used, but not when B is used (the
example does not allow for that because the value is hard coded), how
would i do it?

B.

Dec 7 '06 #1
5 8462
Multiply by -1 :-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Dec 7 '06 #2
Serge Rielau wrote:
Multiply by -1 :-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Grrrrrrrrr! :)

The actual query uses text fields, though one is calculated percentage
that when chosen need to be in desending order.

Hmm..., i'd hate to admit this, but multiplying by -1 would actually
work. Now i'm not sure if i should love you or hate you, Serge. But for
now, thanx! The requestor will be very happy that we have a solution.
:)

B.

Dec 8 '06 #3
I couldn't understand original requirement.
If following expression is accepted
CASE col1 WHEN 1 THEN col2 DESC ELSE col3 ASC END
Or using Serge's way
CASE col1 WHEN 1 THEN -col2 ELSE col3 END ASC
I can't know order of whole rows.

For example: How do you want order following 6 rows?

Col1 col2 col3
---- ---- ----
3 1 2 (row 1)
4 2 4 (row 2)
1 2 1 (row 3)
1 3 6 (row 4)
2 3 3 (row 5)
1 1 5 (row 6)

If col1 = 1, then take col2 and ordered
3 (row 4)
2 (row 3)
1 (row 6)

If col1 <1, then take col3 and ordered
2 (row 1)
3 (row 5)
4 (row 2)

How these all 6 rows ordered?

Dec 8 '06 #4
Tonkuma wrote:
I couldn't understand original requirement.
If following expression is accepted
CASE col1 WHEN 1 THEN col2 DESC ELSE col3 ASC END
Or using Serge's way
CASE col1 WHEN 1 THEN -col2 ELSE col3 END ASC
I can't know order of whole rows.

For example: How do you want order following 6 rows?

Col1 col2 col3
---- ---- ----
3 1 2 (row 1)
4 2 4 (row 2)
1 2 1 (row 3)
1 3 6 (row 4)
2 3 3 (row 5)
1 1 5 (row 6)

If col1 = 1, then take col2 and ordered
3 (row 4)
2 (row 3)
1 (row 6)

If col1 <1, then take col3 and ordered
2 (row 1)
3 (row 5)
4 (row 2)

How these all 6 rows ordered?
The CASE is actually based on a parameter passed to a stored PROCEDURE,
which we actually call Sort_By. So, in actually, it's something like:

ORDER BY
col1,
col2,
CASE Sort_BY
WHEN 'Percentage' THEN calculated_col1 DESC
WHEN 'Item' THEN col4
WHEN 'Serial#' Then col5
END

I provided the example for quick testing.

B.

Dec 8 '06 #5
Brian Tkatch wrote:
Is there a way to do ORDER BY with DESC inside a CASE statement? That
is, given more than one choice for an ORDER BY based on a CASE
statement, but only one of the choices will also use DESC (others rely
on the default of ASC).

For example:

While this works:

WITH A(A, B) AS (VALUES (1,2), (2,1), (3,4)) SELECT A, B FROM A ORDER
BY CASE 1 WHEN 1 THEN A ELSE 2 END DESC

These do not work:

WITH A(A, B) AS (VALUES (1,2), (2,1), (3,4)) SELECT A, B FROM A ORDER
BY CASE 1 WHEN 1 THEN A DESC ELSE 2 END

WITH A(A, B) AS (VALUES (1,2), (2,1), (3,4)) SELECT A, B FROM A ORDER
BY CASE 1 WHEN 1 THEN A ELSE 2 END CASE 1 WHEN 1 THEN DESC END

If i only wanted DESC when A is used, but not when B is used (the
example does not allow for that because the value is hard coded), how
would i do it?

B.
OK, silly me. I can provide two separate CASE statements with DESC
after one.

ORDER BY CASE something WHEN something THEN col1 END desc, CASE
something WHEN something_else THEN col1 END

B.

Dec 8 '06 #6

### This discussion thread is closed

Replies have been disabled for this discussion.