Select max, with tiebreaker

Question

I have a road_insp table:

create table road_insp
(
    insp_id integer,
    road_id integer,
    insp_date date,
    condition number,
    insp_length number
);

--Run each insert statement, one at a time.
INSERT INTO road_insp (insp_id, road_id, insp_date, condition, insp_length)  VALUES (1, 100, #1/1/2017#, 5.0, 20);
INSERT INTO road_insp (insp_id, road_id, insp_date, condition, insp_length)  VALUES (2, 101, #2/1/2017#, 5.5, 40);
INSERT INTO road_insp (insp_id, road_id, insp_date, condition, insp_length)  VALUES (3, 101, #3/1/2017#, 6.0, 60);
INSERT INTO road_insp (insp_id, road_id, insp_date, condition, insp_length)  VALUES (4, 102, #4/1/2018#, 6.5, 80);
INSERT INTO road_insp (insp_id, road_id, insp_date, condition, insp_length)  VALUES (5, 102, #5/1/2018#, 7.0, 100);
INSERT INTO road_insp (insp_id, road_id, insp_date, condition, insp_length)  VALUES (6, 102, #5/1/2018#, 7.5, 120);

+---------+---------+-----------+-----------+-------------+
| insp_id | road_id | insp_date | condition | insp_length |
+---------+---------+-----------+-----------+-------------+
|       1 |     100 | 1/1/2017  |         5 |          20 |
|       2 |     101 | 2/1/2017  |       5.5 |          40 |
|       3 |     101 | 3/1/2017  |         6 |          60 |
|       4 |     102 | 4/1/2018  |       6.5 |          80 |
|       5 |     102 | 5/1/2018  |         7 |         100 |
|       6 |     102 | 5/1/2018  |       7.5 |         120 |
+---------+---------+-----------+-----------+-------------+

I can select the most recent inspection, per road:

 SELECT 
    b.insp_id, 
    b.road_id, 
    b.insp_date, 
    b.condition, 
    b.insp_length
FROM 
    road_insp b
WHERE 
    b.insp_date=(
                   select 
                        max(insp_date) 
                    from 
                        road_insp a 
                    where 
                        a.road_id = b.road_id 
                     );

+---------+---------+-----------+-----------+-------------+
| insp_id | road_id | insp_date | condition | insp_length |
+---------+---------+-----------+-----------+-------------+
|       1 |     100 | 1/1/2017  |         5 |          20 |
|       3 |     101 | 3/1/2017  |         6 |          60 |
|       5 |     102 | 5/1/2018  |         7 |         100 |
|       6 |     102 | 5/1/2018  |       7.5 |         120 |
+---------+---------+-----------+-----------+-------------+

However, as you can see, there can be multiple inspections per road, per date (inspections #5 and #6). The result is more than one record being returned per road.

Instead, where there are multiple inspections per road, per date, I would like to break the tie by selecting only the longest inspection.

+---------+---------+-----------+-----------+-------------+
| insp_id | road_id | insp_date | condition | insp_length |
+---------+---------+-----------+-----------+-------------+
|       1 |     100 | 1/1/2017  |         5 |          20 |
|       3 |     101 | 3/1/2017  |         6 |          60 |
|       6 |     102 | 5/1/2018  |       7.5 |         120 | <--Largest length.
+---------+---------+-----------+-----------+-------------+

How can I do this in an MS Access query?

Answer 1

You can use order by and top instead:

SELECT ri.*
FROM road_insp as ri
WHERE ri.insp_id = (select top 1 ri2.insp_id      --removed brackets on top(1)
                    from road_insp as ri2 
                    where ri2.road_id = ri.road_id 
                    order by ri2.insp_date desc, ri2.insp_length desc,
                             ri2.insp_id
                   );