If one of two values exists they take priority over other values, otherwise select random

Question

Post reply

If one of two values exists they take priority over other values, otherwise select random

.Netter

SSCrazy

Points: 2476
More actions
June 13, 2013 at 6:26 am

#278568

Hello,
I have the below sql statement, where im trying to return a columns value depending on what value is in side.
I have a table called orderhandsets which is associated to the table Order
each order can have more then 1 row linked to it,
so i could have an order 1234
and inside orderhandsets i could have 5 rows assigned to order 1234
the two values im looking for are
1 = new
3 = port
which are inside the column oh.OrderType
if 3 exists then i need to return this as its this value takes priority over all other values, if 3 DOES NOT exists i need to check for1 and return it, if neither of them exists i just need to pull the value thats inside the column
this is my statement
select top(1) oh.OrderType
from OrderHandsets oh
where oh.OrderId = co.OrderId and (oh.Ordertype = 3 or (oh.OrderType =1 or oh.OrderType not in(1,3)))
see now inside the where clause im first checking for 3, if that doesnt exists then i look for one, otherwise just get any other value, as you can also see im using Top(1) which again is causing the problem because if i have rows like the attached pic it always returns one when i need it to return 3
Can someone help me achieve what im looking for?
So check for 3 first,
if that doesnt exists check for 1
if none of the above exists then just pull the value from the column

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

select top(1) oh.OrderType

from OrderHandsets oh

where oh.OrderId = co.OrderId

ORDER BY CASE

WHEN Ordertype = 3 THEN -2

WHEN Ordertype = 1 THEN -1

ELSE oh.OrderType END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

.Netter SSCrazy Points: 2476 More actions · Answer 2

.Netter

SSCrazy

Points: 2476

June 13, 2013 at 6:34 am

#1623670

simple as that!!

Thanks so much!!!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

You're welcome - thanks for the feedback 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

.Netter SSCrazy Points: 2476 More actions · Answer 4

Would you please be able to explain to me what these two lines do

WHEN Ordertype = 3 THEN -2

WHEN Ordertype = 1 THEN -1

Because to me when its 3 that looks like it will return 1?

and when its one it will return 0 due to the minuses

HanShi SSC-Dedicated Points: 33506 More actions · Answer 5

Try this (assuming there are default no negative OrderTypes):

select top(1)

oh.OrderType

from OrderHandsets oh

where oh.OrderId = co.OrderId

order by

case oh.Ordertype

when 3 then -2

when 1 then -1

else 0

end

ASC

Depending on the value of Ordertype a new value is assigned and this new value is used to order the resultset.

Oh, I see others have allready posted this solution...

Sorry for the double answer.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

.Netter SSCrazy Points: 2476 More actions · Answer 6

Hi,

Im still confused on why negative numbers are used because from my eyes when u look at

WHEN Ordertype = 3 THEN -2

WHEN Ordertype = 1 THEN -1

if its 3 -2 is looks like it should be 1?

or

if its 1 -1 it should be 0?

how can this be?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

.Netter (6/13/2013)
Hi,
Im still confused on why negative numbers are used because from my eyes when u look at
WHEN Ordertype = 3 THEN -2
WHEN Ordertype = 1 THEN -1
if its 3 -2 is looks like it should be 1?
or
if its 1 -1 it should be 0?
how can this be?

This snippet will show you the result evaluated by the CASE expression:

SELECT

oh.OrderType,

x.MyOrderSequence

FROM OrderHandsets oh

CROSS APPLY (

SELECT MyOrderSequence = CASE

WHEN Ordertype = 3 THEN -2

WHEN Ordertype = 1 THEN -1

ELSE oh.OrderType END

) x

ORDER BY x.MyOrderSequence

Your rows will be ordered by the result of the CASE.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

HanShi SSC-Dedicated Points: 33506 More actions · Answer 8

You are confused about the CASE statement. In this case it does NOT calculate a new value (i.e. 3 - 2 = 1) but it assigns a new value (i.e. 3 is replaced by -2).

So for the ORDER BY clause the original value of Ordertype is replaced with the new value defined in the CASE statement. This new value is used to determine the sort order.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

.Netter SSCrazy Points: 2476 More actions · Answer 9

Thanks for the snippet, and additional information.