Posted by Roy Harvey on 12/13/06 13:55

A few different approaches.

SELECT *
FROM Purchases as A
WHERE PurchaseDate =
(select min(PurchaseDate) from Purchases as B
where A.VehicleID = B.VehicleID)

SELECT *
FROM Purchases as A
WHERE NOT EXISTS
(select * from Purchases as B
where A.VehicleID = B.VehicleID
and A.PurchaseDate > B.PurchaseDate)

SELECT *
FROM Purchases as A
WHERE PurchaseID =
(select TOP 1 PurchaseID from Purchases as B
where A.VehicleID = B.VehicleID
order by PurchaseDate)

Roy Harvey
Beacon Falls, CT

On 13 Dec 2006 05:09:34 -0800, roberthornsby@hotmail.com wrote:

>Hi,
>Please can you help me with this query which I am struggling with?
>Here is a simplified version of the table I am trying to work with
>
>VehicleId, PurchaseId, PurchaseDate, Comment
>
>1, 1, 03/03/2006, 'customer has a big nose'
>1, 79, 04/04/2006, 'it's raining'
>1, 8, 05/05/2006, 'man, i keep selling this vehicle'
>2, 412, 02/02/2006, 'I break for lunch in 10 minutes'
>2, 5, 03/03/2006, 'I wonder what's on TV tonight'
>3, 2, 05/05/2006, 'I am the angel of death, destroyer of worlds'
>
>I need to select only the rows for the first time the vehicle is sold
>(specifically I need the comment). I need to return
>
>1, 1, 03/03/2006, 'customer has a big nose'
>2, 412, 02/02/2006, 'I break for lunch in 10 minutes'
>3, 2, 05/05/2006, 'I am the angel of death, destroyer of worlds'
>
>Purchase Id cannot be guaranteed to be in ascending date order.
>
>Can anyone help please?
>Thanks
>Rob

[Back to original message]