How to Generate Row number Partition by two column match in sql

Question

Tbl1

---------------------------------------------------------
Id      Date        Qty      ReOrder
---------------------------------------------------------
1       1-1-18      1        3
2       2-1-18      0        3
3       3-1-18      2        3
4       4-1-18      3<      >3
5       5-1-18      2        3
6       6-1-18      0        3
7       7-1-18      1        3
8       8-1-18      0        3
---------------------------------------------------------

I want the result like below

---------------------------------------------------------
Id      Date        Qty      ReOrder
---------------------------------------------------------
1       1-1-18      1        3
5       5-1-18      2        3
---------------------------------------------------------

if ReOrder not same with Qty then date will be same upto after reorder=Qty

Answer 1

You can use cumulative approach with row_number() function :

select top (1) with ties *
from (select *, max(case when qty = reorder then 'v' end) over (order by id desc) grp
      from table
     ) t
order by row_number() over(partition by grp order by id); 

Unfortunately this will require SQL Server, But you can also do:

select *
from (select *, row_number() over(partition by grp order by id) seq
      from (select *, max(case when qty = reorder then 'v' end) over (order by id desc) grp
            from table
           ) t
     ) t
where seq = 1;