Query help please

[roberthornsby]

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

[Jason Lepack]

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
ro***********@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

[Jason Lepack]

I now understand access subqueries. They're different from Oracle.

This one query does what you want.
SELECT T1.VehicleID, tbl_purchases.PurchaseID, T1.MinDate,
tbl_purchases.Comment
FROM [SELECT tbl_purchases.VehicleID, Min(tbl_purchases.PurchaseDate)
AS MinDate
FROM tbl_purchases
GROUP BY tbl_purchases.VehicleID]. AS T1 INNER JOIN tbl_purchases ON
(T1.MinDate = tbl_purchases.PurchaseDate) AND (T1.VehicleID =
tbl_purchases.VehicleID);

Cheers,
Jason Lepack

Jason Lepack wrote:

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
ro***********@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

[Roy Harvey]

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, ro***********@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