sql server (alternate):
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
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 [quoted text, click to view] 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
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 [quoted text, click to view] 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
> 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
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 [quoted text, click to view] 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
Don't see what you're looking for? Try a search.
|
