'SQL Order by, Partiton by using Row_Number()

Following is my table and i want the output as mentioned below

Input Table

if object_id('tempdb..#tmp') is not null
begin
    drop table #tmp
end
go

select * into #tmp from
(
    select 1 as Priority,'INT' as TranType,'2022-02-15' as instDate union all
    select 2 as Priority,'PRIN' as TranType,'2022-02-15' as instDate union all
    select 7 as Priority,'ODC' as TranType,'2022-01-01' as instDate union all
    select 3 as Priority,'CHDIS' as TranType,'2022-01-01' as instDate union all
    select 1 as Priority,'INT' as TranType,'2022-03-15' as instDate union all
    select 2 as Priority,'PRIN' as TranType ,'2022-03-15' as instDate 
)t

select * from #tmp

Output Table

S.No TranType instDate
1 INT 15-Feb-22
2 PRIN 15-Feb-22
3 INT 15-Mar-22
4 PRIN 15-Mar-22
5 CHDIS 01-Mar-22
6 ODC 01-Mar-22


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source