January 8, 2008 at 3:07 pm
Hi!
For the Orders table (let's assume for the Northwind database), I'm trying
to get the order id of the latest order for every customer.
That means that the result should be one record per customer and that would
display CustomerID and OrderID.
Any ideas?
Thanks,
Assaf
January 8, 2008 at 3:31 pm
How about this:
select
CustomerID,
max(SalesOrderID)
from
Sales.SalesOrderHeader
group by
CustomerID
order by
CustomerID
😎
January 8, 2008 at 4:13 pm
Lynn, do you know the difference between "the maximum order id" and "the order id of the latest order"?
To get the the order id of the latest order you need to have OrderDate involved somewhere.
Assaf, if a Customer placed 2 or more orders at the same day - which one you want to display?
_____________
Code for TallyGenerator
January 8, 2008 at 4:24 pm
Code suggested was based on AdventureWorks database. OrderID is an identity column, therefore (and I checked) the maximum OrderID is associated with the latest order for each customer.
I looked at the data before I posted. If you know your data, you know what you can do in your query.
😎
January 8, 2008 at 4:39 pm
Hi!
Thanks for the swift replies.
I'm obviously not working on a Northwind project in the real life.
There are some more constraints in my where clause, so only one record at most can come out per customer.
Cheers,
Assaf
January 8, 2008 at 4:40 pm
To do it where OrderID may not be the max based on orderdate, this would work. It does not take into account if two orders are entered on the same day, but it forms the basis for additional work.
select
soh1.CustomerID,
soh1.SalesOrderID
from
Sales.SalesOrderHeader soh1
where
soh1.OrderDate = (select max(soh2.OrderDate) from Sales.SalesOrderHeader soh2 where soh1.CustomerID = soh2.CustomerID)
order by
soh1.CustomerID
😎
January 8, 2008 at 4:42 pm
Lynn Pettis (1/8/2008)
Code suggested was based on AdventureWorks database. OrderID is an identity column, therefore (and I checked) the maximum OrderID is associated with the latest order for each customer.I looked at the data before I posted. If you know your data, you know what you can do in your query.
😎
Oh, yeah...
And you put couple of watchdogs in there to guarantee that noone could posibly enter something not in the order you expected.
I believe OP used Northwind database just for example. I doubt his real task gonna be limited to the data in it.
And you must be really happy if you never heard about back dated orders or orders being entered into the system by 2 or more operators at the same time.
_____________
Code for TallyGenerator
January 8, 2008 at 4:50 pm
Sergiy,
I really don't care what you have to have to say. I am fully aware of all the things you mentioned, I worked for 11 years with a company the processed over 600 orders daily with an average daily revenue of $100,000. What truely matters is knowing your data, and that is what the OP knows. He used the AdventrueWorks database as an example. Having the database available, I was able to review the data and make a determination for writing a query (know the data).
If there are other things going on with the OP's data, then he needs to provide that additional info if he wants a more accurate assist with the problem.
Try being more helpful rather than flaming everyone.
😎
January 8, 2008 at 4:51 pm
assaf_levi (1/8/2008)
Hi!There are some more constraints in my where clause, so only one record at most can come out per customer.
I understand you need 1 record per customer.
You did not specify which one you would select if 2 or more records have the same OrderDate?
_____________
Code for TallyGenerator
January 8, 2008 at 5:21 pm
Hi!
Lynn's latest code helped me get an idea of how to get myself started.
I'll have to dig myself into it but it's definitely given me a way out of my impasse.
For your question Sergiy, assume that in case of duplicate orders for same customer and same orderdatetime, a version exists. In this case I want the order with the highest version number.
Cheers,
Assaf
January 8, 2008 at 7:26 pm
assaf_levi (1/8/2008)
Hi!Lynn's latest code helped me get an idea of how to get myself started.
Lynn's code gives you absolutely wrong idea.
For your question Sergiy, assume that in case of duplicate orders for same customer and same orderdatetime, a version exists. In this case I want the order with the highest version number.
Don't know what do you mean by "version" but if to assume that you need order with bigger OrderId than this must do the job:
[Code]
SELECT O.CustomerID, MAX(O.OrderID) as LatestOrderID
FROM Orders O
INNER JOIN
(SELECT CustomerID, MAX(OrderDate) AS LastOrderDate
FROM Orders
GROUP BY CustomerID
) T ON T.CustomerID = O.CustomerID AND O.OrderDate = T.LastOrderDate
GROUP BY O.CustomerID
[/Code]
_____________
Code for TallyGenerator
January 8, 2008 at 7:38 pm
Wrong because I use a correlated subquery or wrong because I didn't take into account you may have more than one order for a given day by a customer (I noted in my post that I hadn't done that), or wrong just becasue you say so?
January 8, 2008 at 7:53 pm
Lynn, wrong because it returns biggest OrderID, not OrderId for the latest order,
In that test database you used it's just happened that OrderDate and OrdeID correlate, that's why your query accidently returned correct result.
But it's not the rule. It's absolutely allowed situation when record with bigger OrderID will have earlier OrderDate.
Therefore you failed to implement correct logic in your query.
Sit down.
E.
_____________
Code for TallyGenerator
January 9, 2008 at 6:06 am
Thanks Sergiy!
Your code does deliver.
However, what are the venomous comments for?
People might be afraid of trying to help others, which is the whole point here, isn't it?
Thanks again to all of you helping there,
Assaf
January 9, 2008 at 8:27 am
He's Sergiy. Enough said about that.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply