Can anyone help me to design a quey

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 2000 Forums

SQL Server Development (2000)

Can anyone help me to design a quey

Author

Topic

Petehe
Starting Member

20 Posts

Posted - 2003-10-24 : 03:23:04

Table A
CustomerID
CustomerName

Table B
OrderID
OrderDate
CustomerID
OrderPrice

I would like to have a list with all customers and their last order price.

My Sql is
Select CustomerName, orderPrice from A right outer join B on A.CustomerID=B.CustomerID and orderDate=(select max(orderDate) from B)

But "orderDate=(select max(orderDate) from B)" have some problem,
it try to get the max order date for all customers not base on on the max order of each customer.

can any one help me with that?

Thanks.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-24 : 03:57:48

select CustomerName,
(select top 1 OrderPrice from B where B.CustomerID=A.CustomerID
order by OrderDate desc) LatestOrderPrice
from A