Select up to last 2 values per grouping and display them side by side

A table is periodically updated (incrementing load_id). How to select

• for each value in column name
• the last two value in column count
• where last is given by max(load_id) per day
• and put them side by side?

Input

name,day,count,load_id
John,10,9,14
John,9,5,12
John,9,7,13
John,8,5,12
John,8,2,11
Paul,9,0,12
Paul,9,1,13
Paul,7,1,11
Paul,7,9,10
Mark,7,10,11
Mark,7,11,10
Ned,7,0,10

Step 1 – Last record per each day

name,day,count
John,10,9
John,9,7
John,8,5
Paul,9,1
Paul,7,1
Mark,7,10
Ned,7,0

Output

name,last_day_last_count,prev_day_last_count
John,9,7
Paul,1,1
Mark,10,NULL
Ned,0,NULL

Attempt

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

Fiddle

>Solution :

Aggregate grouping by name and day to find the MAXimum load_id and the corresponding count and then use ROW_NUMBER to number the rows by DESCending load_id for each person and finally PIVOT to get the latest 2 values as columns, rather than rows:

SELECT *
FROM   (
  SELECT name,
         MAX(count) KEEP (DENSE_RANK LAST ORDER BY load_id) AS count,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY MAX(load_id) DESC) AS rn
  FROM   test
  GROUP BY name, day
)
PIVOT(
  MAX(count) FOR rn IN (
    1 AS last_day_count,
    2 AS prev_day_count
  )
)

Or else use ROW_NUMBER in two passes:

SELECT *
FROM   (
  SELECT name,
         count,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY load_id DESC)
           AS rn_by_person
  FROM   (
    SELECT name,
           load_id,
           count,
           ROW_NUMBER() OVER (PARTITION BY name, day ORDER BY load_id DESC)
             AS rn_by_day
    FROM   test
  )
  WHERE  rn_by_day = 1
)
PIVOT(
  MAX(count) FOR rn_by_person IN (
    1 AS last_day_count,
    2 AS prev_day_count
  )
)

Which, for the sample data, both output:

To get the corresponding load_ids as well:

SELECT *
FROM   (
  SELECT name,
         MAX(count) KEEP (DENSE_RANK LAST ORDER BY load_id) AS count,
         MAX(load_id) AS load_id,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY MAX(load_id) DESC) AS rn
  FROM   test
  GROUP BY name, day
)
PIVOT(
  MAX(count) AS count,
  MAX(load_id) AS load_id
  FOR rn IN (
    1 AS last_day,
    2 AS prev_day
  )
)

or

SELECT *
FROM   (
  SELECT name,
         load_id,
         count,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY load_id DESC)
           AS rn_by_person
  FROM   (
    SELECT name,
           load_id,
           count,
           ROW_NUMBER() OVER (PARTITION BY name, day ORDER BY load_id DESC)
             AS rn_by_day
    FROM   test
  )
  WHERE  rn_by_day = 1
)
PIVOT(
  MAX(count) AS count,
  MAX(load_id) AS load_id
  FOR rn_by_person IN (
    1 AS last_day,
    2 AS prev_day
  )
)

Which both output:

fiddle