Select most recent inspection, break tie between inspections from same year

dategreatest-n-per-grouporacleoracle-12c

I have a road_insp table:

create table road_insp
(
    insp_id int,
    road_id int,
    insp_date date,
    length number(10,2)
);

insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 1, 100, to_date('01/JAN/2013 6:00:00AM','DD/MON/YY HH:MI:SSAM'), 1000);
insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 2, 100, to_date('01/JAN/2014 7:00:00AM','DD/MON/YY HH:MI:SSAM'), 1000);
insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 3, 100, to_date('01/JAN/2015 8:00:00AM','DD/MON/YY HH:MI:SSAM'), 1000);

insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 4, 100, to_date('01/JAN/2016 9:00:00AM','DD/MON/YY HH:MI:SSAM'), 750);
insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 5, 100, to_date('01/FEB/2016 10:00:00AM','DD/MON/YY HH:MI:SSAM'), 250);

commit;

select
    insp_id,
    road_id,
    to_char(insp_date, 'DD/MON/YY HH:MI:SSAM') insp_date_formatted,
    length
from road_insp;

   INSP_ID    ROAD_ID INSP_DATE_FORMATTED      LENGTH
---------- ---------- -------------------- ----------
         1        100 01/JAN/13 06:00:00AM       1000
         2        100 01/JAN/14 07:00:00AM       1000
         3        100 01/JAN/15 08:00:00AM       1000

         4        100 01/JAN/16 09:00:00AM        750
         5        100 01/FEB/16 10:00:00AM        250

I'm able to select the most recent inspection of each road_id using this query:

select
    insp_id,
    road_id,
    to_char(insp_date, 'DD/MON/YY HH:MI:SSAM') insp_date_formatted,
    length
from 
(
      select
        r.insp_id
        ,r.road_id
        ,r.insp_date
        ,r.length
        ,row_number() over (partition by road_id order by insp_date desc) rn
    from user.road_insp r
) 
where
     rn =1
;


   INSP_ID    ROAD_ID INSP_DATE_FORMATTED      LENGTH
---------- ---------- -------------------- ----------
         5        100 01/FEB/16 10:00:00AM        250

However, rather than simply just select the most recent inspection, I want to do do something a bit more complex:

Get the most recent inspection
But if there is more than one inspection in that inspection year, break the tie by getting the longest inspection

   INSP_ID    ROAD_ID INSP_DATE_FORMATTED      LENGTH
---------- ---------- -------------------- ----------
         4        100 01/JAN/16 09:00:00AM        750

In other words, if there are multiple inspections in the latest inspection year, it doesn't really matter which one came first (within the context of the year). It's more important which inspection was longest.

How can I do this?

Best Answer

You can modify the order by clause in the window function:

row_number() over 
    (partition by road_id 
     order by extract(year from insp_date) desc,   -- latest year
              length desc                          -- higher length
    ) as rn

Tested in dbfiddle.uk (only in version 11g).

In version 12c, you could also use the FETCH FIRST syntax.

The wanted PARTITION BY makes things more difficult than a simple FETCH though. We'll have to use a LATERAL inline view or CROSS APPLY:

select
    r.*
from
    ( select distinct road_id
      from road_insp
    ) d,
  lateral
    ( select
          ri.insp_id,
          ri.road_id,
          to_char(ri.insp_date, 'DD/MON/YY HH:MI:SSAM') insp_date_formatted,
          ri.length
      from road_insp ri
      where ri.road_id = d.road_id
      order by extract(year from ri.insp_date) desc,
               ri.length desc
      fetch first 1 rows only
    ) r 
;

Related Solutions

SQL Server – How to Select All Most Recent Records

Your query includes a lot of tables and columns that aren't listed in your sample data. From this query you can join and pull in other things:

;WITH lastMessage AS
(
    SELECT Id, IdMain, IdReply, DatePosted, rn = ROW_NUMBER() OVER 
      (PARTITION BY COALESCE(IdMain, Id) ORDER BY DatePosted DESC, Id)
    FROM dbo.Message
)
SELECT t.Id, t.IdMessage, m.IdMain, m.IdReply, m.DatePosted
  FROM lastMessage AS m
  INNER JOIN dbo.Target AS t
  ON m.Id = t.IdMessage
  WHERE m.rn = 1;

SQLfiddle demo

MySQL/PostgreSQL – How to Select One Most Recent Post for Each Author

If you aim is to have queries with maximum efficiency, none of the above queries is really the best. Not always at least.

Efficiency depends on many different things, like the specific DBMS, the specific version (different versions have different improvements on the optimizer and the available syntax), the type of columns, the indexes available, the size of the tables and distribution of values, the hardware the server is running, the configuration settings etc.

You should always test various different ways of writing the queries, on your tables, with the sizes and distribution you expect to have on production, with your hardware and configuration settings, to decide which rewritings of the queries should be kept.

This specific kind of query is often called greatest-n-per-group (there is even a tag for it!) and under certain assumptions, one of the many ways to write them, is often quite efficient in both MySQL and PostgreSQL. It uses a LATERAL join in Postgres, which is available in 9.3+ versions (in SQL Server lingo CROSS/OUTER APPLY) and a simulation of this join in MySQL.

The assumptions are that the number of authors (the attribute we group by on) is small, compared to the number of posts (the table where we apply the group by). It's also best if there is an index or a table to find all the distinct author_id values and an additional index on the posts table for the group by.

This solution to the greatest-n-per-group problem matches also your request about ties, as it returns always one result per group. If you want to be precise about which one (of the tied) will be returned, the ORDER BY in the subquery can be modified (to ORDER BY pi.date DESC, pi.id DESC or ORDER BY pi.date DESC, a.name for example).

Query in PostgreSQL:

SELECT p.* 
FROM authors AS a
   , LATERAL 
       ( SELECT pi.*
         FROM posts AS pi
         WHERE pi.author_id = a.author_id
         ORDER BY pi.date DESC
         LIMIT 1
       ) AS p ;

Query in MySQL:

SELECT p.* 
FROM authors AS a
  JOIN posts AS p
    ON p.id =
       ( SELECT pi.id
         FROM posts AS pi
         WHERE pi.author_id = a.author_id
         ORDER BY pi.date DESC
         LIMIT 1
       ) ;

The useful index is on posts (author_id, date, id) for MySQL and or on posts (author_id, date DESC) for Postgres.

Needless to say again but before using any of the above, they should be tested in your environment and cross tested against all the many other versions/rewritings of the query. In Postgres for example, the DISTINCT ON syntax can be used in version older than 9.3. The resulting query is more compact than the LATERAL and might be more efficient, under different data distributions. Query:

SELECT DISTINCT ON (author_id) p.*
FROM posts AS p
ORDER BY p.author_id,
         p.date DESC ;

Best Answer

Related Solutions

SQL Server – How to Select All Most Recent Records

MySQL/PostgreSQL – How to Select One Most Recent Post for Each Author

Related Question