CODEWITHSUNDEEP
sqlsql-serversql-server-2008
MDMalik
MDMalik

Reputation: 4001

SQL Filter in Select Value

My query is giving me this result

StoreID  BlockID DateOfEntry UPC            OnExit      
AA003    AAA416   29-09-13   6281100021049  Available   
AA003    AAA417   29-09-13   6281100021049  Low OOS-Trigger

OnExit is calculated runtime based on the formula (not seen). Now the only difference between the two values for the same store is there different BlockID

What I desire is something like this
If Low OOS-Trigger then display only that. If not then display Available.

Desired Result should appear something like this

StoreID  DateOfEntry UPC            OnExit      
AA003    29-09-13   6281100021049   Low OOS-Trigger

I have tried doing Distinct but still resulted in 2 rows. provide above. Any hints.

Query: 

select * from dbv1.viewTable
where dateofentry='2013-09-29'
and storeid='AA003' and BlockID in ('AAA416','AAA417')
and [upc]  ='6281100021049'

Upvotes: 0

Views: 261

Answers (1)

John Woo
John Woo

Reputation: 263693

The query below ROW_NUMBER() which is a ranking functions that generates sequential number. It partitions the records via StoreID and sorted it by the value of OnExit using CASE statement. This will only display 1 record for every StoreID.

SELECT  StoreID, BlockID, DateOfEntry, UPC, OnExit
FROM    
        (
            SELECT  StoreID, BlockID, DateOfEntry, UPC, OnExit,
                    ROW_NUMBER() OVER (PARTITION BY StoreID, UPC 
                    ORDER BY CASE WHEN OnExit = 'Low OOS-Trigger' THEN 1
                                ELSE 0 END) rn
            FROM    dbv1.viewTable
            -- WHERE    dateofentry = '2013-09-29'
            --      AND storeid = 'AA003' 
            --      AND BlockID in ('AAA416','AAA417')
            --      AND [upc]  = '6281100021049'
        ) a
WHERE   a.rn = 1

Upvotes: 2

Related Questions