Conditional sorting

I'm attempting to sort using the following conditions:
If the score is greater or equal to passing score, sort by score
descending, test taken date descending. If score is less than passing
score, sort by test taken date descending, score descending.
Expected results:
3 95 2005-01-02 00:00:00
1 90 2005-02-02 00:00:00
2 50 2004-02-02 00:00:00
4 75 2003-03-02 00:00:00
Actual results:
2 50 2004-02-02 00:00:00
4 75 2003-03-02 00:00:00
3 95 2005-01-02 00:00:00
1 90 2005-02-02 00:00:00
How can I achieve the expected results?
Here's the SQL I've tried so far:
CREATE TABLE CourseData (
id int,
SCORE int,
TESTTAKEN smalldatetime,
PASSSCORE int
)
INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
VALUES(1,90,'2005-02-02',80)
INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
VALUES(2,50,'2004-02-02',80)
INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
VALUES(3,95,'2005-01-02',80)
INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
VALUES(4,75,'2003-03-02',80)
SELECT
id,
SCORE,
TESTTAKEN
FROM
CourseData
ORDER BY
CASE WHEN SCORE >= PASSSCORE THEN SCORE ELSE TESTTAKEN END DESC,
CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN ELSE SCORE END DESC
DROP TABLE CourseData
TIA.
PTry,
...
ORDER BY
CASE WHEN SCORE >= PASSSCORE THEN SCORE end desc,
case when SCORE < PASSSCORE then TESTTAKEN END DESC,
CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN END DESC,
CASE WHEN SCORE < PASSSCORE THEN SCORE END DESC
go
AMB
"go559@.hotmail.com" wrote:

> I'm attempting to sort using the following conditions:
> If the score is greater or equal to passing score, sort by score
> descending, test taken date descending. If score is less than passing
> score, sort by test taken date descending, score descending.
> Expected results:
> 3 95 2005-01-02 00:00:00
> 1 90 2005-02-02 00:00:00
> 2 50 2004-02-02 00:00:00
> 4 75 2003-03-02 00:00:00
> Actual results:
> 2 50 2004-02-02 00:00:00
> 4 75 2003-03-02 00:00:00
> 3 95 2005-01-02 00:00:00
> 1 90 2005-02-02 00:00:00
> How can I achieve the expected results?
> Here's the SQL I've tried so far:
> CREATE TABLE CourseData (
> id int,
> SCORE int,
> TESTTAKEN smalldatetime,
> PASSSCORE int
> )
> INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
> VALUES(1,90,'2005-02-02',80)
> INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
> VALUES(2,50,'2004-02-02',80)
> INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
> VALUES(3,95,'2005-01-02',80)
> INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
> VALUES(4,75,'2003-03-02',80)
> SELECT
> id,
> SCORE,
> TESTTAKEN
> FROM
> CourseData
> ORDER BY
> CASE WHEN SCORE >= PASSSCORE THEN SCORE ELSE TESTTAKEN END DESC,
> CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN ELSE SCORE END DESC
> DROP TABLE CourseData
>
>
> TIA.
> P
>|||Thank you for the reply, it solved my issue. However, I'm not quite
clear why it works. Using the data set I posted, I evaluated the ORDER
BY clause using your solution and mine.
Example 1:
VALUES(1,90,'2005-02-02',80)
Mine:
ORDER BY SCORE DESC, TESTTAKEN DESC
Yours:
ORDER BY SCORE DESC, NULL DESC, TESTTAKEN DESC, NULL DESC
Example 2:
VALUES(2,50,'2004-02-02',80)
Mine:
ORDER BY TESTTAKEN DESC, SCORE DESC
Yours:
ORDER BY NULL DESC, TESTTAKEN DESC, NULL DESC, SCORE DESC
Since the null else clauses are ignored, then it appears that both
solutions produce the same ORDER BY clause. Would you please let me
know what I'm missing?
TIA.
P
Alejandro Mesa wrote:
> Try,
> ...
> ORDER BY
> CASE WHEN SCORE >= PASSSCORE THEN SCORE end desc,
> case when SCORE < PASSSCORE then TESTTAKEN END DESC,
> CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN END DESC,
> CASE WHEN SCORE < PASSSCORE THEN SCORE END DESC
> go
>
> AMB
>
> "go559@.hotmail.com" wrote:
>
passing|||Try with two rows, each one belonging to a diff group.
AMB
"go559@.hotmail.com" wrote:

> Thank you for the reply, it solved my issue. However, I'm not quite
> clear why it works. Using the data set I posted, I evaluated the ORDER
> BY clause using your solution and mine.
> Example 1:
> VALUES(1,90,'2005-02-02',80)
> Mine:
> ORDER BY SCORE DESC, TESTTAKEN DESC
> Yours:
> ORDER BY SCORE DESC, NULL DESC, TESTTAKEN DESC, NULL DESC
> Example 2:
> VALUES(2,50,'2004-02-02',80)
> Mine:
> ORDER BY TESTTAKEN DESC, SCORE DESC
> Yours:
> ORDER BY NULL DESC, TESTTAKEN DESC, NULL DESC, SCORE DESC
> Since the null else clauses are ignored, then it appears that both
> solutions produce the same ORDER BY clause. Would you please let me
> know what I'm missing?
> TIA.
> P
> Alejandro Mesa wrote:
> passing
>|||On 10 Feb 2005 11:29:02 -0800, go559@.hotmail.com wrote:

>Thank you for the reply, it solved my issue. However, I'm not quite
>clear why it works. Using the data set I posted, I evaluated the ORDER
>BY clause using your solution and mine.
>Example 1:
>VALUES(1,90,'2005-02-02',80)
>Mine:
>ORDER BY SCORE DESC, TESTTAKEN DESC
>Yours:
>ORDER BY SCORE DESC, NULL DESC, TESTTAKEN DESC, NULL DESC
(snip)
Hi P,
Not exactly. Your ORDER BY clause is:
Now you should know that CASE is an expression that can ony result in one
datatype. Which datatype depends on the datatypes of the various THEN
clauses and the WHEN clause. For the first CASE, the datatype of THEN
SCORT is int; the datatype of TESTTAKEN is smalldatetime. According to the
datatype precedence rules, SCORE will have to be converted to
smalldatetime.
The implicit conversion of int to smalldatetime works like this: take the
value of the integer and add that number of days to 19000101. So if the
score is 60, the result after converting to smalldatetime will be 19000302
and that's the value that will be used for the sorting.
It's actually quite easy to see for yourself why your order by won't work
as you'd like it to: just add the CASE expressions from the ORDER BY to
the SELECT clause:
SELECT
id,
SCORE,
TESTTAKEN,
CASE WHEN SCORE >= PASSSCORE THEN SCORE ELSE TESTTAKEN END AS ordering1,
CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN ELSE SCORE END AS ordering2
FROM
CourseData
ORDER BY
CASE WHEN SCORE >= PASSSCORE THEN SCORE ELSE TESTTAKEN END DESC,
CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN ELSE SCORE END DESC
In Alejandro's version, no different datatypes are mixed within the same
CASE expressions. Therefore, there are no implicit conversions and
everything is working as expected.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)