Select latest from customers table?

Hi,

I'm having issues trying to select the latest entry in a customers table, where there are many entries per customer.  Example:


Table: Orders

ID     CustomerID     DateAdded                   PartName
2      5                     28/02/2005 16:41:37   Brush
3      7                     28/02/2005 16:41:37   Spade
4      5                     29/02/2005 16:41:37   Ladder
7      3                     27/02/2005 16:41:37   Gel
8      3                     28/02/2005 16:41:37   Frame
10    5                     30/02/2005 16:41:37   Paint
     

I need a select that returns to me the latest entry per customer id.  So from the above recordset I would get returned:

ID     CustomerID     DateAdded                   PartName
7      3                     27/02/2005 16:41:37   Gel
3      7                     28/02/2005 16:41:37   Spade
10    5                     30/02/2005 16:41:37   Paint


i.e. the latest 1 order, by dateadded, from each customer id   ordered by dateadd asc


Any ideas?
LVL 3
XanderPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HilaireCommented:
Please try

Select ID,     CustomerID,     DateAdded,                   PartName
from yourtable A
where ID = (select top 1 ID from yourtable where CustomerID = A.CustomerID order by DateAdded desc)

HilaireCommented:
Oops I had missed table name

Select ID,     CustomerID,     DateAdded,                   PartName
from Orders A
where ID = (select top 1 ID from Orders where CustomerID = A.CustomerID order by DateAdded desc)
XanderPAuthor Commented:
ok, this explains alot for me thanks :)

..but I'm still in a jam!   I actually have a second identifier in the orders table.

You see a customer might have different orders and have many items in that order.  Example:

Table: Orders

ID     CustomerID    OrderID   DateAdded                   PartName
2      5                    50          28/02/2005 16:41:37   Brush
3      7                    33          28/02/2005 16:41:37   Spade
4      5                    50          29/02/2005 16:41:37   Ladder
7      3                    23          27/02/2005 16:41:37   Gel
8      3                    24          28/02/2005 16:41:37   Frame
10    5                    65          30/02/2005 16:41:37   Paint
     

I need a select that returns to me the latest entry per customer id.  So from the above recordset I would get returned:

ID     CustomerID    OrderID   DateAdded                   PartName
3      7                    33          28/02/2005 16:41:37   Spade
4      5                    50          29/02/2005 16:41:37   Ladder
7      3                    23          27/02/2005 16:41:37   Gel
8      3                    24          28/02/2005 16:41:37   Frame
10    5                    65          30/02/2005 16:41:37   Paint


So it pick id 3 out, as customer 7 has only one order

It picks ID 4 and 10 from customer 5 as they are in different orders, but drops ID 2 since it belongs to order 50, but id=4 is newer

Does that makes sense?


Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

HilaireCommented:
OK then try

Select ID,     CustomerID,     DateAdded,                   PartName
from Orders A
where ID = (select top 1 ID from Orders where CustomerID = A.CustomerID and OrderID = A.OrderID order by DateAdded desc)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
XanderPAuthor Commented:
Spot on! thx Hilaire :)
XanderPAuthor Commented:
Ah! :(   ..looks like the code doesn't work afterall :(

Can this be reopened please?


Code:
Select ID,     CustomerID,     DateAdded,                   PartName
from Orders A
where ID = (select top 1 ID from Orders where CustomerID = A.CustomerID and OrderID = A.OrderID order by DateAdded desc)


The problem is with "and OrderID = A.OrderID"  line, it seems to only take the latest orderid of the same value.

Example
Table: Orders

ID     CustomerID    OrderID   DateAdded                   PartName
2      5                    50          28/02/2005 16:41:37   Brush
3      7                    33          28/02/2005 16:41:37   Spade
4      5                    50          29/02/2005 16:41:37   Ladder
7      3                    23          27/02/2005 16:41:37   Gel
8      3                    50          28/02/2005 16:41:37   Frame
10    5                    65          30/02/2005 16:41:37   Paint

Where you can see custid 3 and 5 have an orderid of 50, it would only take the latest order of the orderid=50, in this case, row id 4.

Any thoughts?
XanderPAuthor Commented:
Erm, lol scrap that it does work...the code I used working on the theory of this code was incorrectly typed.

HilaireCommented:
glad you could sort it out by yourself
have a nice day
Hilaire
XanderPAuthor Commented:
Thanks for checking back Hilaire. :)

I've got an extended question based on your code, found here:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21481732.html

;)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.