CODEWITHSUNDEEP
sql-server
Dee
Dee

Reputation: 45

SQL - Shortening the query

I have the below query.

with cte0 as (
    SELECT * FROM #Temp1 WHERE [Label] = 'B'
),
cte1 as (
    SELECT * FROM #Temp1 WHERE [Label] = 'A' AND [Value] > 20
)
SELECT * FROM cte0
UNION ALL
SELECT * FROM cte1

The condition is very simple. I'm taking the all the records from a table which contains two columns namely - [Label] and [Value], excluding the values < 20 only for column where [Label] = "A"

Data :

Label   Value
A   24
A   18
A   15
A   35
A   27
A   37
B   18
B   29
B   18
B   16
B   16

I'm using the query mentioned above to do that. But I wanted to do that in a single line without using any cte.

CONDITION: Need to select all the records excluding values less than 20 only for [Label] that has "A". 9 rows to be returned.

Help me out to overcome it.

Thanks in advance.

Upvotes: 0

Views: 259

Answers (5)

SMA
SMA

Reputation: 37023

Try this:

SELECT *
FROM #Temp1
WHERE Label = 'B'
OR    (Label = 'A' 
    AND value > 20)

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460148

Use OR and proper parenthesis:

SELECT * FROM #Temp1 
WHERE  [Label] = 'B'
OR    ([Label] = 'A' AND [Value] > 20)

Demo

Upvotes: 2

Steve Oh
Steve Oh

Reputation: 1159

you can use OR here:

SELECT * FROM #Temp1 WHERE ( [Label] = 'B' ) OR ([Label] = 'A' AND [Value] > 20 )

Upvotes: 0

rev1995_prof
rev1995_prof

Reputation: 21

Try something like this:

SELECT * FROM #Temp1 WHERE ([Label] = 'A' AND [Value] > 20) OR (Label] = 'B')

Upvotes: 2

Dgan
Dgan

Reputation: 10285

try this:

SELECT * FROM #Temp1 WHERE [Label] = 'B'
UNION ALL
SELECT * FROM #Temp1 WHERE [Label] = 'A' AND [Value] > 20

OR

SELECT * FROM #Temp1 WHERE ([Label] = 'B' ) OR ([Label] = 'A' AND [Value] > 20)

Upvotes: 0

Related Questions