CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user2499976
user2499976

Reputation: 23

Select Negative values only if exists in SQL Server

i am new to sql server. i am having some problem when selecting values for report. i am having the following columns:

name, snap, current, percent.

i want to select only negative values if exists in the column "percent" if not select +ve values only.

for example:

if i am having -1, -10, 0, 10 then it should select only -1, -10.

i tried the following query, but its giving both +ve and -ve values.

selectname, snap, current, percent 
where percent = 
CASE WHEN percent < 0 then percent 
when percent > 0 then percent

i don't know how to use if else in where condition. Can anyone help me out ? Thanks in advance.

Upvotes: 2

Views: 51258

Answers (2)

Paritosh
Paritosh

Reputation: 11560

You can use IF EXISTS to check the condition and then get the desired result

IF EXISTS(SELECT * FROM TABLENAME WHERE percent < 0)
BEGIN
    SELECT [name], [snap], [current], [percent] FROM TABLENAME WHERE [percent] < 0
END
ELSE BEGIN
    SELECT [name], [snap], [current], [percent] FROM TABLENAME WHERE [percent] > 0
END

Upvotes: 3

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

You can try using UNION ALL to separate two conditions. Due to them being opposite, only one part of query will return rows.

SELECT [name], [snap], [current], [percent]
FROM Table1
WHERE EXISTS (SELECT * FROM Table1 WHERE [percent] < 0) 
AND [percent] < 0

UNION ALL

SELECT [name], [snap], [current], [percent] 
FROM Table1
WHERE NOT EXISTS (SELECT * FROM Table1 WHERE [percent] < 0)
AND [percent] > 0

SQLFiddle DEMO

Demo with non-negative values

Upvotes: 2

Related Questions