Row_Number() over()

declare @t as table
(
  storeID int,
  orderID int,
  upc int,
  code_Nbr int    
)

insert into @t(storeID,orderID,upc,code_Nbr)
values
(4480,101,10000,3), 
(4481,101,10000,2),

(4485,102,20000,3), 
(4486,102,20000,3),

(4491,102,10000,2), 
(4492,102,10000,1),

(4470,103,10000,3) 

select * from @t

 

 

select 
  ROW_NUMBER() over(order by orderID,upc,code_Nbr desc) as rowIndex,
  storeID,orderID,upc,code_Nbr
from @t
order by rowIndex

 

select a.* 
from (
    select 
     ROW_NUMBER() over(order by orderID,upc,code_Nbr desc) as rowIndex,
     storeID,orderID,upc,code_Nbr
    from @t
) as a
inner join ( 
    --分组拿排序前面的 min(rowIndex)
    select orderID,upc,min(rowIndex) as rowIndex
    from (
        --排序号
        select
         ROW_NUMBER() over(order by orderID,upc,code_Nbr desc) as rowIndex,
         orderID,upc
        from @t
    ) as temp group by orderID,upc
) as b on a.rowIndex=b.rowIndex

 

优化

select * 
from (
    select 
     ROW_NUMBER() over (partition by orderID,upc order by code_Nbr desc) as rowIndex,
     storeID,orderID,upc,code_Nbr
    from @t
) as a 

 

select * 
from (
    select 
     ROW_NUMBER() over (partition by orderID,upc order by code_Nbr desc) as parNumber,
     storeID,orderID,upc,code_Nbr
     ,ROW_NUMBER() over (order by orderID,upc,code_Nbr desc) as rowNumber --新加
    from @t
) as a 
where parNumber=1