I'm sure there is an easy answer to this

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have an aggregate query which groups on product codes, then uses Max to
find the latest invoice date. I then want to see the amount recorded against
the latest invoice but can't figure out how to do it. This figure will not
necessarily be the highest value so can't use Max again. Any help much
appreciated. Thought this would be a 2 minute query to create!

Sheila
 
Sheila said:
Hi

I have an aggregate query which groups on product codes, then uses Max to
find the latest invoice date. I then want to see the amount recorded against
the latest invoice but can't figure out how to do it. This figure will not
necessarily be the highest value so can't use Max again. Any help much
appreciated. Thought this would be a 2 minute query to create!
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT product_code, invoice_date, amount
FROM table_name As t
WHERE invoice_date = (SELECT Max(invoice_date)
FROM table_name
WHERE product_code = t.product_code)

Substitute your table's name for "table_name" and your column names for
my column names.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiTKKIechKqOuFEgEQJJzgCg1hYIUJQTDkc/dMWYyB+UE6RxKXIAoKiH
ix9gHbG2cDQBcfu6Qk3Vzqil
=aot8
-----END PGP SIGNATURE-----
 
That works beautifully - if you can tell me how it works so I can use in
other situations I'd be most grateful

Thanks a lot

Sheila
 
Sheila said:
That works beautifully - if you can tell me how it works so I can use in
other situations I'd be most grateful
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT product_code, invoice_date, amount
FROM table_name As t
WHERE invoice_date = (SELECT Max(invoice_date)
FROM table_name
WHERE product_code = t.product_code)

The subquery finds the latest invoice date for the each product code the
main query is "reading." Then the main query "reads" only the row that
has the same invoice date [the Max(invoice_date)] that the subquery
found for that product code.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiddOoechKqOuFEgEQLX5gCdHeNUr4A/G77I5NyW0US99EYDHV0AoIlG
YMk9BwiP6sHC6r2/uLsk1cts
=GEas
-----END PGP SIGNATURE-----
 
Back
Top