Get the last price

[mattia]

Hi all, I have a table where for each product I have a list of
components; every component has a price an a date (usually different
prices per date).
I want to have a table where, for each product, I have the unique
components and the last saved price.

Example:

IDProduct | Component | Date | Price
----------+-----------+------------+-------
2 | 1 | 01/06/2008 | 12
3 | 2 | 07/12/2008 | 8
5 | 1 | 03/04/2009 | 5,5
6 | 3 | 01/05/2008 | 6
5 | 1 | 12/06/2009 | 11
2 | 1 | 21/08/2008 | 10
2 | 2 | 17/11/2008 | 7
3 | 2 | 05/09/2008 | 10

Result:

IDProduct | Component | Date | Price
----------+-----------+------------+-------
2 | 1 | 21/08/2008 | 10
2 | 2 | 17/11/2008 | 7
3 | 2 | 07/12/2008 | 8
6 | 3 | 01/05/2008 | 6
5 | 1 | 12/06/2009 | 11

Thanks, Mattia

 

[John Spencer]

You might have to use two queries to get the result you want.
First Query - Saved as qLatest
SELECT IDProduct, Component, Max([Date]) as LastDate
FROM [Your Table]
GROUP BY IDProduct, Component

Second query
SELECT [Your Table].*
FROM [Your Table] INNER JOIN qLatest
ON [Your Table].IdProduct = qLatest.IdProduct
AND [Your Table].Component = qLatest.Component
AND [Your Table].[Date] = qLatest.LastDate

Another option would be to use a correlated subquery. This is slower but it
does have the advantage of being updateable.
SELECT [Your Table].*
FROM [Your Table]
WHERE [Date] =
(SELECT Max[Date]
FROM [Your Table] as Temp
WHERE Temp.IdProduct = [Your Table].IdProduct
AND Temp.Component = [Your Table].Component

If you can't build a query in SQL view, post back and someone should be able
to give you step-by-step instructions on how to build this using Query Design
View.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[mattia]

Il Mon, 20 Jul 2009 15:36:36 -0400, John Spencer ha scritto:

You might have to use two queries to get the result you want. First
Query - Saved as qLatest
SELECT IDProduct, Component, Max([Date]) as LastDate FROM [Your Table]
GROUP BY IDProduct, Component

Second query
SELECT [Your Table].*
FROM [Your Table] INNER JOIN qLatest
ON [Your Table].IdProduct = qLatest.IdProduct AND [Your Table].Component
= qLatest.Component AND [Your Table].[Date] = qLatest.LastDate

Another option would be to use a correlated subquery. This is slower
but it does have the advantage of being updateable. SELECT [Your
Table].*
FROM [Your Table]
WHERE [Date] =
(SELECT Max[Date]
FROM [Your Table] as Temp
WHERE Temp.IdProduct = [Your Table].IdProduct AND Temp.Component =
[Your Table].Component

If you can't build a query in SQL view, post back and someone should be
able to give you step-by-step instructions on how to build this using
Query Design View.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi all, I have a table where for each product I have a list of
components; every component has a price an a date (usually different
prices per date).
I want to have a table where, for each product, I have the unique
components and the last saved price.

Example:

IDProduct | Component | Date | Price
----------+-----------+------------+-------
2 | 1 | 01/06/2008 | 12
3 | 2 | 07/12/2008 | 8
5 | 1 | 03/04/2009 | 5,5
6 | 3 | 01/05/2008 | 6
5 | 1 | 12/06/2009 | 11
2 | 1 | 21/08/2008 | 10
2 | 2 | 17/11/2008 | 7
3 | 2 | 05/09/2008 | 10

Result:

IDProduct | Component | Date | Price
----------+-----------+------------+-------
2 | 1 | 21/08/2008 | 10
2 | 2 | 17/11/2008 | 7
3 | 2 | 07/12/2008 | 8
6 | 3 | 01/05/2008 | 6
5 | 1 | 12/06/2009 | 11

Thanks, Mattia

Thanks for the reply. Just one thing, why are you saying that the second
option is slower? It is because the two use a primary key in the
selection and then the fact that is indexed speed everything up?

 

[John Spencer]

The last option is slower because it runs the query in the where clause
ONCE for every record in the table. With a few hundred records in your
table you won't see difference with a few hundred thousand records you
will definitely see a difference.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Il Mon, 20 Jul 2009 15:36:36 -0400, John Spencer ha scritto:

You might have to use two queries to get the result you want. First
Query - Saved as qLatest
SELECT IDProduct, Component, Max([Date]) as LastDate FROM [Your Table]
GROUP BY IDProduct, Component

Second query
SELECT [Your Table].*
FROM [Your Table] INNER JOIN qLatest
ON [Your Table].IdProduct = qLatest.IdProduct AND [Your Table].Component
= qLatest.Component AND [Your Table].[Date] = qLatest.LastDate

Another option would be to use a correlated subquery. This is slower
but it does have the advantage of being updateable. SELECT [Your
Table].*
FROM [Your Table]
WHERE [Date] =
(SELECT Max[Date]
FROM [Your Table] as Temp
WHERE Temp.IdProduct = [Your Table].IdProduct AND Temp.Component =
[Your Table].Component

If you can't build a query in SQL view, post back and someone should be
able to give you step-by-step instructions on how to build this using
Query Design View.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi all, I have a table where for each product I have a list of
components; every component has a price an a date (usually different
prices per date).
I want to have a table where, for each product, I have the unique
components and the last saved price.

Example:

IDProduct | Component | Date | Price
----------+-----------+------------+-------
2 | 1 | 01/06/2008 | 12
3 | 2 | 07/12/2008 | 8
5 | 1 | 03/04/2009 | 5,5
6 | 3 | 01/05/2008 | 6
5 | 1 | 12/06/2009 | 11
2 | 1 | 21/08/2008 | 10
2 | 2 | 17/11/2008 | 7
3 | 2 | 05/09/2008 | 10

Result:

IDProduct | Component | Date | Price
----------+-----------+------------+-------
2 | 1 | 21/08/2008 | 10
2 | 2 | 17/11/2008 | 7
3 | 2 | 07/12/2008 | 8
6 | 3 | 01/05/2008 | 6
5 | 1 | 12/06/2009 | 11

Thanks, Mattia

Thanks for the reply. Just one thing, why are you saying that the second
option is slower? It is because the two use a primary key in the
selection and then the fact that is indexed speed everything up?