CODEWITHSUNDEEP
sqlsql-serverdistinct
Oskar Eriksson
Oskar Eriksson

Reputation: 855

SQL Server - SELECT DISTINCT depending on another column value

I got a table like this, and there's on input parameter RevParam:

Key Rev IsCurrent
=====================
abc 0   TRUE
def 0   TRUE
ghj 2   FALSE
ghj 0   TRUE
klm 0   TRUE
def 1   FALSE
abc 1   FALSE
abc 2   FALSE

Where I want Key to be a unique value. If RevParam is set all rows with that Rev value should be viewed, the missing Key should have: revision 0. Like this:

Result if RevParam = 1      

abc 1   FALSE
def 1   FALSE
ghj 0   TRUE
klm 0   TRUE

Result if revisionParameter = 0     

abc 0   TRUE
def 0   TRUE
ghj 0   TRUE
klm 0   TRUE

This has been buggering me all day, please help me out!

Upvotes: 0

Views: 380

Answers (2)

John Woo
John Woo

Reputation: 263723

WITH records
AS
(
    SELECT  [key], [rev], [IsCurrent],
            ROW_NUMBER() OVER(PARTITION BY [key]
            ORDER BY CASE WHEN [rev] = 1          -- <<== change REV value here
            THEN 0 ELSE 1 END) rn
    FROM    tableName
    WHERE   [Rev] IN (1,0)
)
SELECT  [key], [rev], [IsCurrent]
FROM    records 
WHERE   rn = 1

Upvotes: 4

Mrinal Bhattacharjee
Mrinal Bhattacharjee

Reputation: 1406

Result if RevParam = 1

abc 1 FALSE def 1 FALSE ghj 0 TRUE klm 0 TRUE

it has rev value 0 as well as 1. Please specify clearly

Upvotes: 0

Related Questions