get last order of all customers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

get last order of all customers

Author

Topic

MyronCope
Starting Member

46 Posts

Posted - 2011-02-02 : 12:59:31

using sql server 2005.

I have table setup as follows

Table: Orders

Col: CustomerID
Col: OrderID
Col: OrderDate
Col: FieldA
Col: Field B and so on

so a given Customer can have many orders in this table however I only want to return the last order (using OrderDate) for each customer. how do you do this?
EX: CustA could have 3 orders (records) in this table, so I want the latest order, and so on..
I will need to return OrderID and FieldA and FieldB.

What I tried was this:


SELECT CustomerID, OrderID, orderDate
FROM dbo.Orders
WHERE orderDate = (SELECT MAX(orderDate) FROM dbo.Orders)
ORDER BY customerID

And while this gets me closer, it did not return what I really need: what it returns is the latest orders by orderDate for each customer however there are many customers that placed 2 or more orders on the same date, so that returns more than one order for each customer when I only want to return one order for each customer, I just need the latest order.

I tried doing max on both the date and the orderID but then I get no records being returned.

any examples are greatly appreciated. thanks
MC

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-02 : 13:43:04

select CustomerID, OrderID, orderDate from
(select row_number() over (partition by CustomerID order by OrderDate DESC) rownum,* from Orders)dt
where rownum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

MyronCope
Starting Member

46 Posts

Posted - 2011-02-02 : 14:13:30

quote:
Originally posted by webfred

select CustomerID, OrderID, orderDate from
(select row_number() over (partition by CustomerID order by OrderDate DESC) rownum,* from Orders)dt
where rownum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

thanks for the response. what is the "dt" value, is that an alias? and if I want to join this to a OrderAddress to get the address for each customer do I join with the Orders table or the dt table?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-02 : 15:20:40

dt is the name of the derived table and you can join to the dt table.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources