find max value of unique part numbers

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

Guest

I don't know the best way of asking this question, but here goes...

I have a primary data table that looks something like this:

Serial Number Date testvalue
0001 2/25/06 1000
0001 2/25/06 1234
0001 2/27/06 5675
0015 2/24/06 1534
0015 2/26/06 9521
0027 2/24/06 1699
0027 2/25/06 8640

Each serial number appears multiple times but with different testvalues. I
want a query that returns each serial number only once and with the maximum
of the test value for that serial number. Based on the above data the
desired result would look like:

Serial Number Date testvalue
0001 2/27/06 5675
0015 2/24/06 9521
0027 2/25/06 8640

How can I do this?

Rob
 
br549 said:
I don't know the best way of asking this question, but here goes...

I have a primary data table that looks something like this:

Serial Number Date testvalue
0001 2/25/06 1000
0001 2/25/06 1234
0001 2/27/06 5675
0015 2/24/06 1534
0015 2/26/06 9521
0027 2/24/06 1699
0027 2/25/06 8640

Each serial number appears multiple times but with different testvalues. I
want a query that returns each serial number only once and with the maximum
of the test value for that serial number. Based on the above data the
desired result would look like:

Serial Number Date testvalue
0001 2/27/06 5675
0015 2/24/06 9521
0027 2/25/06 8640

How can I do this?

Rob
Click to expand...

I have a similar query:
Right click on the Date field and click on totals.
Select "Max" for the totals field.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT [Serial Number], [Date], testvalue As hi_value
FROM table_name As T
WHERE testvalue = (SELECT MAX(testvalue) FROM table_name
WHERE [serial number] = T.[serial number])

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

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

iQA/AwUBRAYHOYechKqOuFEgEQKa0gCg6D7U+vEHLzXYQOXtFu6z9k3J6xAAoNFP
bf451ew/7phknCmVoE8X7D0E
=sEo9
-----END PGP SIGNATURE-----
 
Back
Top