Select statement

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: simon (simon.zupan_at_stud-moderna.si)
Date: 07/26/04 

Date: Mon, 26 Jul 2004 16:12:52 +0200

I have table orderItems.
The quantity and some other values can be changed all the time until order
is closed.
I get the last quantity and date of items in my order:

SELECT n1.productID,n1.quantity FROM
(select orderID,productID,max(orderDate)as orderDate FROM orderItems group
by orderID,productID)as T1 INNER JOIN orderItems n1 ON
n1.orderID=T1.orderID AND n1.productID=T1.productID AND
n1.orderDate=T1.orderDate
WHERE my orderID=200

Now I would like to get also the first quantity of items in my order:

SELECT n1.productID,n1.quantity,
firstQuantity=(SELECT top 1 quantity FROM orderItems WHERE
productID=n1.productID AND orderID=n1.orderID
ORDER BY orderDate),
firstOrderDate=(SELECT top 1 orderDate FROM orderItems WHERE
productID=n1.productID AND orderID=n1.orderID
ORDER BY orderDate)
FROM
(select orderID,productID,max(orderDate)as orderDate FROM orderItems group
by orderID,productID)as T1 INNER JOIN orderItems n1 ON
n1.orderID=T1.orderID AND n1.productID=T1.productID AND
n1.orderDate=T1.orderDate
WHERE my orderID=200

It works fine, but it seems stupid to me to write select statement for each
value of the first order I want to know. Can I combine them somehow:

SELECT n1.productID,n1.quantity,
(SELECT top 1 firstQuantity=quantity,firstOrderDate=firstOrderDate FROM
orderItems WHERE productID=n1.productID AND orderID=n1.orderID ORDER BY
orderDate),
FROM
(select orderID,productID,max(orderDate)as orderDate FROM orderItems group
by orderID,productID)as T1 INNER JOIN orderItems n1 ON
n1.orderID=T1.orderID AND n1.productID=T1.productID AND
n1.orderDate=T1.orderDate
WHERE my orderID=200

thank you,
Simon