Retrieving max row

[shnaeem]

Hi,

I have a table which have cust_id and product_id and order date.
Customer 1 can order product 1 many times only dateandtime would be different. I need to get the latest order for each (customer, product) combination
C1 ,p1 could have many rows i need only latest one. and same is true for c1, p2.... c2, p1...


I can't get the query straight.
So far i have come up with

select * from Order join
(select Customer_ID, Product_id , max(datetimestamp) as max_date
from Order
group by Customer_ID, Product_id) A
on Order.Customer_id = A.Customer_id and Order.Product_id = A.Product_id


This join query is giving all the records instead of the latest order

 

[balves]

If you just want the customer id, product id and date, you don't need a sub-select:

select Customer_ID, Product_id , max(datetimestamp) as max_date
from Order
group by Customer_ID, Product_id

If you want everything from the Order row:

Select A.*
INNER JOIN
(select Customer_ID, Product_id , max(datetimestamp) as
max_date
from Order
group by Customer_ID, Product_id) B

ON A.Customer_Id = B.Customer_Id
AND A.Product_Id = B.Product_Id
AND A.datetimestamp = B.max_date

 

[shnaeem]

but this query is returning all the rows in each of the group instead of one row per group which has highest datetime.

 

[grahamm]

I agree with balves. This should do it, according to the requirements in your post:

select CustId,OrderId,max([datetimestamp]) from [order] group by CustId, OrderId

It returns the latest entry for each CustId, OrderId combo.

 

[shnaeem]

Believe me it does not work.
It gives me a set for eery combination of customer and product. I need only one row per combination.

 

[75cl]

have you try this:

select distinct(CustId),OrderId,max([datetimestamp]) from [order] group by CustId, OrderId

 

[cmmrfrds]

If datetimestamp is an Sql Server timestamp data type then I don't believe the max function will work. If this is true you could try to order the data by the datetimestamp desc and do a sub query to pick off the TOP 1. So, the sub query would start with (SELECT TOP 1........) and then group by customer and product in the outer query.

select Customer_ID, Product_id,
(select top 1 datetimestamp
from Order X
where X.Customer_ID = Y.Customer_ID and X.Product_ID =
Y.Product_ID
order by datetimestamp desc) as max_date
from Order Y
Group by Y.Customer_ID, Y.Product_ID
Order by Y.Customer_ID, Y.Product_ID

 

[Guest_imported]

Try this:

SELECT * from orders A
where orderdate = (select max(orderdate) from order where cID = A.cID AND pID = A.pID)