Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (name, day, count, load_id) AS
SELECT 'John', 10, 9, 14 FROM DUAL UNION ALL
SELECT 'John', 9, 5, 12 FROM DUAL UNION ALL
SELECT 'John', 9, 7, 13 FROM DUAL UNION ALL
SELECT 'John', 8, 5, 12 FROM DUAL UNION ALL
SELECT 'John', 8, 2, 11 FROM DUAL UNION ALL
SELECT 'Paul', 9, 0, 12 FROM DUAL UNION ALL
SELECT 'Paul', 9, 1, 13 FROM DUAL UNION ALL
SELECT 'Paul', 7, 1, 11 FROM DUAL UNION ALL
SELECT 'Paul', 7, 9, 10 FROM DUAL UNION ALL
SELECT 'Mark', 7, 10, 11 FROM DUAL UNION ALL
SELECT 'Mark', 7, 11, 10 FROM DUAL UNION ALL
SELECT 'Ned', 7, 0, 10 FROM DUAL;
12 rows affected

WITH RankedData AS (
  SELECT
    name,
    day,
    count,
    load_id,
    ROW_NUMBER() OVER (PARTITION BY name, day ORDER BY load_id DESC) AS rn
  FROM test
)

SELECT *
FROM RankedData
WHERE rn = 1
ORDER BY name, day DESC


NAME
DAY
COUNT
LOAD_ID
RN


John
10
9
14
1

John
9
7
13
1

John
8
5
12
1

Mark
7
10
11
1

Ned
7
0
10
1

Paul
9
1
13
1

Paul
7
1
11
1


WITH RankedData AS (
  SELECT
    name,
    day,
    count,
    load_id,
    ROW_NUMBER() OVER (PARTITION BY name, day ORDER BY load_id DESC) AS rn
  FROM test
)

SELECT
  rd.name,
  rd.day,
  rd.count AS last_count,
  prev_rd.count AS previous_count
FROM RankedData rd
LEFT JOIN RankedData prev_rd ON rd.name = prev_rd.name
  AND rd.day = prev_rd.day
  AND prev_rd.rn = 2
WHERE rd.rn = 1


NAME
DAY
LAST_COUNT
PREVIOUS_COUNT


John
8
5
2

John
9
7
5

Mark
7
10
11

Paul
7
1
9

Paul
9
1
0

John
10
9
null

Ned
7
0
null

NAME	DAY	COUNT	LOAD_ID	RN
John	10	9	14	1
John	9	7	13	1
John	8	5	12	1
Mark	7	10	11	1
Ned	7	0	10	1
Paul	9	1	13	1
Paul	7	1	11	1