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)

conditional logic in stored procedure

Hello.

Looking for a smarter way to code the following. I have a stored
procedure I will be passing several variables to. Some times, some of
the fields used in a WHERE clause will not be passed, and I would like
to avoid having to code a bunch of if statements to set the executing
code. For example, below I would only like to execute the LIKE
conditions only when the variable in question is not NULL. I did a
test and if the variable is set to null, obviously the select does not
return what I'm expecting.

if @.switch = "B"
SELECT * from ikb where
ikbtitle like @.ins1 and
ikbtitle like @.ins2 and
ikbtitle not like @.ins3 and
ikbbody like @.ins1 and
ikbbody like @.ins2 and
ikbbody not like @.ins3
end

Thanks for any help or information with this.>> I would only like to execute the LIKE conditions only when the
variable in question is not NULL. I did a test and if the variable is
set to null, obviously the select does not return what I'm expecting.
<<

SELECT *
FROM Foobar
WHERE kbtitle LIKE COALESCE(@.ins1, kbtitle)
AND ikbtitle LIKE COALESCE(@.ins2, ikbtitle)
AND ikbtitle NOT LIKE COALESCE(@.ins3, '')
AND ikbbody LIKE COALESCE(@.ins1, ikbbody)
AND ikbbody LIKE COALESCE(@.ins2, ikbbody)
AND ikbbody NOT LIKE COALESCE(@.ins3,'')|||Hi Jason,

Here's one suggestion. Change your params to '%' if they're null.
That way you don't need the IF statement. I would also rewrite the
"not like" clause as it's CPU intensive. - Louis

select @.ins1=isnull(@.ins1,'%')
select @.ins2=isnull(@.ins2,'%')
select @.ins3=isnull(@.ins3,'%')

SELECT * from ikb where
ikbtitle like @.ins1 and
ikbtitle like @.ins2 and
ikbtitle not like @.ins3 and
ikbbody like @.ins1 and
ikbbody like @.ins2 and
ikbbody not like @.ins3

Conditional group by

Hi,

Can anyone help me in writing this sql query, i want to group my select statement depending on the parameter user is passing.

Say when @.group='Cell' I want to group by CellID otherwise different conditions, something like below query but it is not working. I know we can't use case directly in where but please let me know if there is any other work around.

I don't want to use dynamic query and also this is big SP so i dont want to break sp in four conditions.

declare @.group varchar(10)

set @.group='Cell'

select cellid,sum(count)

FROM CellImpressionFact

WHERE ImpressionTypeLevelId = 2

AND ImpressionTypeId = 4

group by

case when group='Cell' then GROUP BY CellId

else group by activityID

end

This is not a good idea really. I would use dynamic SQL to provide this kind of capability if you really need to. It is possible (see code) but I would be very concerned about performance.

create table test
(
grouper int,
grouper2 int,
value decimal(10,5)
)
go
insert into test
select 1,1,10
union all
select 1,2,10
union all
select 1,3,10
union all
select 2,1,10
go
declare @.groupby varchar(10)
set @.groupBy = 'grouper2'

select max(grouper) as grouper,
max(grouper2) as grouper2,
sum(value) as valueSum
from test
group by case when @.groupBy = 'grouper' then grouper else grouper2 end

Note that the grouper2 column is of any value when you group by grouper, and vice versa (say it five times fast.)