CODEWITHSUNDEEP
sqlsql-server
MD Rizvan
MD Rizvan

Reputation: 11

Fetch last record for each Row number

Hi have written query like this:

select Customerid,orderDate, OrderNumber,
       DENSE_RANK() OVER (PARTITION BY Customerid ORDER BY orderDate) "rank"
from [order]

and this produce result:

list customer order details

Here I want to retrieve only latest purchase of each customer like this:

1   2014-04-09 00:00:00.000 543141  6
2   2014-03-04 00:00:00.000 543056  4
3   2014-01-28 00:00:00.000 542986  7

How to achieve this using sql query

Upvotes: 1

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use a subquery:

select o.*
from (select Customerid,orderDate, OrderNumber,
             DENSE_RANK() OVER (PARTITION BY Customerid ORDER BY orderDate DESC) as seqnum
      from [order] o
     ) o
where seqnum = 1;

Upvotes: 1

Related Questions