Posted by Jason Lepack on 12/13/06 13:29

I used two queries:

Query1:
SELECT tbl_purchases.VehicleID, Min(tbl_purchases.PurchaseDate) AS
MinOfPurchaseDate
FROM tbl_purchases
GROUP BY tbl_purchases.VehicleID;

Query2:
SELECT Query1.VehicleID, tbl_purchases.PurchaseID,
Query1.MinOfPurchaseDate, tbl_purchases.Comment
FROM Query1 INNER JOIN tbl_purchases ON (Query1.VehicleID =
tbl_purchases.VehicleID) AND (Query1.MinOfPurchaseDate =
tbl_purchases.PurchaseDate)
GROUP BY Query1.VehicleID, tbl_purchases.PurchaseID,
Query1.MinOfPurchaseDate, tbl_purchases.Comment;

Cheers,
Jason Lepack
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]