For SQL Server I have a table called hcp_funding_packages with

client_sysid (int)
package_level (int)
created_at (datetime)

For each client_sysid, I need the entry for the latest package_level change.

So for example if I have the following

then I need the bold row on the 4th

CodePudding user response：

You can use a combination of LEAD and ROW_NUMBER window functions for this

WITH Changed AS (
    SELECT *,
      CASE WHEN package_level <> LEAD(package_level, 1, -999) OVER (PARTITION BY client_sysid ORDER BY date DESC)
           THEN 1 END AS IsDifferent
    FROM hcp_funding_packages fp
),
Numbered AS (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY client_sysid ORDER BY date DESC) AS rn
    FROM Changed fp
    WHERE IsDifferent = 1
)
SELECT
  client_sysid,
  package_level,
  date
FROM Numbered fp
WHERE rn = 1;

db<>fiddle

Make sure you have an index on (client_sysid, date DESC) if you want this to be performant.