SQL query to select rows with highest vales

[bigAPE]

Hi Guys,

I am struggling with performance on an Access Query.

What I am trying to get is only the LATEST record for each Series and
License where DType = 'AAA'. LATEST is defined as the highest Ver
(Version) number and the most recent DVD number if there are more than
one of the most recent Version

This is a mock of the table I am dealing with.

TABLE: media
+----+-------+--------+---------+-----+------+------------+-----------
+
| id | dtype | series | license | ver | dvd | burnt | notes
|
+----+-------+--------+---------+-----+------+------------+-----------
+
| 1 | AAA | FB01 | AL354E | 1 | 2345 | 21/02/2009 | abcdefg1
|
| 2 | AAA | FB01 | SB522D | 1 | 1323 | 19/06/2009 | abcdefg2
|
| 3 | AAA | FB02 | SB522D | 2 | 2442 | 02/07/2009 | some note
|
| 4 | AAA | FB03 | AL672A | 2 | 1123 | 02/01/2009 | cdefsss1
|
| 5 | AAA | FB03 | AL672A | 3 | 2432 | 05/01/2009 | cdefsss1
|
| 6 | AAA | FB03 | AL672A | 3 | 3335 | 16/01/2009 | cdefsss2
|
| 7 | AAA | FB04 | WE121C | 2 | 1111 | 10/05/2009 | abcdefg
|
| 8 | FFF | WRC5 | NS1234 | 9 | 3333 | 20/01/2009 | gfdeasa
|
+----+-------+--------+---------+-----+------+------------+-----------
+

- ID 1 will be selected as there is no other matching DType, Series
and License
- ID 2 will be ignored because ID 3 is the same DType, Series and
License but with a higher Version
- ID 3 will be selected as it is Version 2 of the media defines in ID
2
- ID 4 will be ignored because ID's 5 & 6 are of a higher version
- ID 5 will be ignored because ID 6 is a higher DVD number with the
same Version
- ID 6 will be selected as it is higher than ID 4 & 5
- ID 7 will be selected as there are no other matches
- ID 8 will be ignored as it is of DType 'FFF'


This is what the result should look like

TABLE: Query1 against Table media
+----+-------+--------+---------+-----+------+------------+-----------
+
| id | dtype | series | license | ver | dvd | burnt | notes
|
+----+-------+--------+---------+-----+------+------------+-----------
+
| 1 | AAA | FB01 | AL354E | 1 | 2345 | 21/02/2009 | abcdefg1
|
| 3 | AAA | FB02 | SB522D | 2 | 2442 | 02/07/2009 | some note
|
| 6 | AAA | FB03 | AL672A | 3 | 3335 | 16/01/2009 | cdefsss2
|
| 7 | AAA | FB04 | WE121C | 2 | 1111 | 10/05/2009 | abcdefg
|
+----+-------+--------+---------+-----+------+------------+-----------
+

I have the following nasty looking nested Select which IS working, but
when run against a large dataset (30,000+ records) it grinds to a halt
and pretty much
crashes Access.

SELECT t1.*
FROM media AS t1
WHERE t1.dtype = 'AAA'
AND t1.dvd = (
SELECT MAX(t2.dvd)
FROM media AS t2
WHERE t2.dtype = 'AAA'
AND t2.series = t1.series
AND t2.license = t1.license
AND t2.ver = (
SELECT MAX(t3.ver)
FROM media AS t3
WHERE t3.dtype = t1.dtype
AND t3.series = t1.series
AND t3.license = t1.license
)
)

I know that is because it is peforming a nested selected for each
record
and that i be possible using an INNER JOIN but I can't work the damn
thing out

Any help or abuse greatly appreciated

Al

 

[vanderghast]

Multiple possible ways. The most trivial is to use two queries, one that
will get the max burnt by series and license:

SELECT dtype, series, license, MAX(burnt) As burntMax
FROM media
WHERE dtype = "AAA"
GROUP BY dtype, series, license


The second query is a new query joining the table media fields with the
fields of the previous query:


SELECT *
FROM media INNER JOIN q
ON media.dtype=q.dtype
AND media.series = q.series
AND media.license = q.license
AND media.burnt = q.burntMax


where q is the name of the saved previous query.



Vanderghast, Access MVP

 

[bigAPE]

Hi Vanderghast,

I didn't think about using multiple chained queries.

The rules are actually MAX(ver) followed by MAX(dvd), not MAX(burnt)
but I think
I can augment the example you have to work with my requirements

Cheers

Al