CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Manoj Nayak
Manoj Nayak

Reputation: 2509

Selective Filter rows based on column Value SQL

I have a table with a keycolumn and other columns as below.

enter image description here

Keycolumn is a combination of col1,col2,col3,col4. For a given key column value I have different values for Col5. If the key column value has multiple values on Col5 then consider anyone other than 'O'. If all staus are 'O' then consider 'O'.

In the above case I should get 4 rows.Each One from Yellow,Green,Blue and Orange

Upvotes: 1

Views: 1022

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

Gordon's query has valid idea, but it doesn't follow your rules about picking the non-'O' row if possible.

WITH
CTE
AS
(
    SELECT
        KeyColumn
        ,Col1
        ,Col2
        ,Col3
        ,Col4
        ,Col5
        ,Col6
        ,ROW_NUMBER() OVER (PARTITION BY KeyColumn
            ORDER BY 
                CASE WHEN Col5 = 'O' THEN 1 ELSE 0 END -- put 'O' last
                ,Col5) as rn -- pick first non-O row
                             -- you can choose other column(s) instead of Col5 here
    FROM YourTable
)
SELECT
    KeyColumn
    ,Col1
    ,Col2
    ,Col3
    ,Col4
    ,Col5
    ,Col6
FROM CTE
WHERE rn = 1
;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269663

In most databases, you would use the ANSI standard row_number() function. Something like this:

select t.*
from (select t.*,
             row_number() over (partition by keycolumn order by keycolumn) as seqnum
      from t
     ) t 
where seqnum = 1;

Upvotes: 1

Related Questions