SQL to find best row in group based on multiple columns?

Question

Let's say I have an Oracle table with measurements in different categories:

CREATE TABLE measurements (
  category CHAR(8),
  value NUMBER,
  error NUMBER,
  created DATE
)

Now I want to find the "best" row in each category, where "best" is defined like this:

It has the lowest errror.
If there are multiple measurements with the same error, the one that was created most recently is the considered to be the best.

This is a variation of the greatest N per group problem, but including two columns instead of one. How can I express this in SQL?

Tim Biegeleisen · Accepted Answer · 2019-03-01T14:11:51.393

Use ROW_NUMBER:

WITH cte AS (
    SELECT m.*, ROW_NUMBER() OVER (PARTITION BY category ORDER BY error, created DESC) rn
    FROM measurements m
)

SELECT category, value, error, created
FROM cte
WHERE rn = 1;

For a brief explanation, the PARTITION BY clause instructs the DB to generate a separate row number for each group of records in the same category. The ORDER BY clause places those records with the smallest error first. Should two or more records in the same category be tied with the lowest error, then the next sorting level would place the record with the most recent creation date first.

SQL to find best row in group based on multiple columns?

1 Answers1