Row number over partition by usage

Question

I would like to filter the below table based on the week column, but only when the year week combination is a max.

In the below example I want my output to only select rows with year 2022 and week 1 as that is the latest year week combination.

I am thinking to use a row number over partition by but did not have any success.

Car  colour   year   week
aaa  red      2021   52
aaa  black    2021   51
bbb  red      2022   1
bbb  yellow   2022   1

MT0 · Answer 1 · 2022-02-09T22:56:22.107

From Oracle 12, you can use:

SELECT *
FROM   table_name
ORDER BY year DESC, week DESC
FETCH FIRST ROW WITH TIES;

Or, in earlier version, you can use:

SELECT car, colour, year, week
FROM   (
  SELECT t.*,
         RANK() OVER (ORDER BY year DESC, week DESC) AS rnk
  FROM   table_name t
)
WHERE  rnk = 1;

Which, for the sample data:

CREATE TABLE table_name (Car, colour, year, week) AS
SELECT 'aaa', 'red',    2021, 52 FROM DUAL UNION ALL
SELECT 'aaa', 'black',  2021, 51 FROM DUAL UNION ALL
SELECT 'bbb', 'red',    2022,  1 FROM DUAL UNION ALL
SELECT 'bbb', 'yellow', 2022,  1 FROM DUAL;

Both output:

CAR COLOUR YEAR WEEK

bbb red 2022 1

bbb yellow 2022 1

db<>fiddle here

Row number over partition by usage

1 Answers1