Need to fetch result row on basis of order of value

user_9840831 0
Viewed 40
Item No    Date   Item Type         Item No   Date     Item Type
12345   12/17/2021  High            12345   12/17/2021  HIGH
23456   12/17/2021  Low             23456   12/17/2021  Low
78901   12/17/2021  Out             78901   12/17/2021  Out
12345   11/17/2021  Low             12345   11/17/2021  Low
23456   11/17/2021  High            23456   11/17/2021  High
78901   11/17/2021  Low             78901   11/17/2021  Low
12345   12/17/2021  HIGH                    
23456   12/17/2021  High                    Result Table
78901   12/17/2021  Low                 
78901   12/17/2021  High         
     Source table

I have source table at right side and require result table at left side. Result are on basis of column 'ITEM TYPE' :- Out, High , Low . IF I have 2 row having different 'ITEM TYPE' then it should select only one value on basis of Out>Low>High. Eg:- If 'ITEM NO' Have value 12345 and it has High , Low and High, then it it should take value as High. If it has Low and High only then it should take it as Low and if it has only High , it will take it as High. Please help me with query , how I can achieve it.

oracle sql-server
1 Answers

I'd rank the rows per item number and date combination, and then query the "first" one only:

SELECT item_no, date, item_type
FROM   (SELECT item_no, date, item_type
               RANK() OVER (PARTITION BY item_no, date
                            ORDER BY CASE item_type WHEN 'Out'  THEN 1
                                                    WHEN 'Low'  THEN 2
                                                    WHEN 'High' THEN 3
                                      END) AS rk
        FROM   my_table) t
WHERE  rk = 1
user_24227760
Related