CODEWITHSUNDEEP
sqlsql-servert-sql
tljubas
tljubas

Reputation: 157

IF THEN in T-SQL

I am working on a DB in T-SQL and I have a problem now.

I have multiple rows with the same name but with a different value in one of the columns (in one row doc is 0 and in the second row doc is 2000), and some rows come just once and with only one value.

Now, I want to select all the ones where I have the 0 in the row, and if there is no 0 then I want to select the one with the value.

Here's sample data:

CREATE TABLE Table1
    ([name] varchar(3), [doc] int, [sum] int)
;

INSERT INTO Table1
    ([name], [doc], [sum])
VALUES
    ('tom', 0, 100),
    ('tom', 2000, 200),
    ('jon', 2000, 200)
;

Now I expect to get the row where TOM has the value of 100, and JON has the value of 200.

Upvotes: 0

Views: 245

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460058

One way, using ROW_NUMBER in a CTE:

WITH cte 
     AS (SELECT [name], 
                [doc], 
                [sum], 
                [rn] = Row_number() 
                       OVER( 
                         partition BY name 
                         ORDER BY CASE WHEN doc=0 THEN 0 ELSE 1 END) 
         FROM   table1) 
SELECT [name], 
       [doc], 
       [sum] 
FROM   cte 
WHERE  rn = 1

DEMO (with your sample data)

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269543

This version uses a window function to count the number of rows with zero value for each name. It then selects either the rows where the value is 0 or the rows with a valid value, using the logic in your question.

select name, value
from (select name, value,
             sum(case when value = 0 then 1 else 0 end) over (partition by name) as NumZeros
      from t
     ) t
 where numZeros = 0 or value = 0

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103447

Assuming your A column cannot have negative values:

select name, min(A)
from yourtable
group by name

If you want the entire row for each of the above, then you have to use a subquery:

select yourtable.* from yourtable
join (
    select name, min(A) [mina]
    from yourtable
    group by name
) a on a.name=yourtable.name and a.[mina]=yourtable.A

Upvotes: 0

Related Questions