get the last order for each customer

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

SQL Server 2005 Forums

Transact-SQL (2005)

get the last order for each customer

Author

Topic

MyronCope
Starting Member

46 Posts

Posted - 2011-02-02 : 12:10:15

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.

any examples are greatly appreciated. thanks
MC

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-02 : 12:42:53

You could use the MAX function or RANK or ROWNUMBER functions. Give those a shot and if you need more help, post back here what you tried and then we can help you with it.

Jim

Everyday I learn something that somebody else already knew

MyronCope
Starting Member

46 Posts

Posted - 2011-02-02 : 12:45:48

quote:
Originally posted by jimf

You could use the MAX function or RANK or ROWNUMBER functions. Give those a shot and if you need more help, post back here what you tried and then we can help you with it.

Jim

Everyday I learn something that somebody else already knew

thanks for the feedback. I think I do need help with the syntax because I tried all 3 but no luck. I was thinking that I should group by the Customer and then get the max of the order.
Do you have any syntax examples of how you would get the value that I'm trying to get?

thanks,

MC

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-02 : 12:48:45

You can adapt the solution here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=156085

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 : 13:06:40

quote:
Originally posted by webfred

You can adapt the solution here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=156085

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

thanks for the advice, I posted what I did there. I'm very close to getting it.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-02 : 13:44:15

I've posted a solution there...

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