CODEWITHSUNDEEP
sqlsql-server
Peter_07
Peter_07

Reputation: 117

Query to return rows with duplicate values based on other column

I have a table in SQL Server, the format is as follows:

enter image description here

I would want to get rows according to the following conditions:

So the result in this case will be row 3, 4 and 5.

Any idea on how to achieve this using SQL query? The table has no primary or unique key.

Upvotes: 0

Views: 224

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use window function row_number()

select * 
from 
   (
   select *, 
         row_number() over(partition by Customer_ID,Order_Number order by date desc) as rn 
   from your_table
   ) t where rn=1

or use co-relates subquery

    select * 
    from t
    where date in (
                   select max(date) 
                   from t t1 
                   where t1.Customer_ID=t.Customer_ID and t1.Order_Number=t.Order_Number
                  )

Upvotes: 2

Related Questions