I have the following data set:

ITEM       | LOCATION   |  STATUS 
iPhone 13  |  10000     |  Inctive
iPhone 13  |  20000     |  Active
iPhone 12  |  20000     |  Inactive
iPhone 11  |  10000     |  Active

I first want to check on Location: 10000, what is the status and get that. If the record doesn't exist for location: 10000, it should check for Location: 20000

The Result Would be

**ITEM       | STATUS** 
iPhone 13    | Inctive
iPhone 12    | Inactive
iPhone 11    | Active

How to do that ?

CodePudding user response：

Assuming you just want to view your data this way, this is a basic ROW_NUMBER query:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY ITEM ORDER BY STATUS DESC) rn
    FROM yourTable t
)

SELECT ITEM, LOCATION, STATUS
FROM cte
WHERE rn = 1;