SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.creative_schedule_status_histories (
  id integer IDENTITY PRIMARY KEY,
  creativeScheduleId varchar(10) NOT NULL
);

CREATE UNIQUE INDEX idx_creativescheduleid_id
  ON dbo.creative_schedule_status_histories (creativeScheduleId ASC, id ASC);
SET SHOWPLAN_TEXT ON;
SELECT
    Q1.id, 
    Q1.creativeScheduleId
FROM 
(
    SELECT 
        CSSH.id, 
        CSSH.creativeScheduleId,
        rn = ROW_NUMBER() OVER (
            PARTITION BY CSSH.creativeScheduleId
            ORDER BY CSSH.id DESC)
    FROM dbo.creative_schedule_status_histories AS CSSH
) AS Q1
WHERE
    Q1.rn = 1
-- Encourage optimizer
ORDER BY
    Q1.creativeScheduleId DESC,
    Q1.id DESC;


StmtText


SELECT
    Q1.id, 
    Q1.creativeScheduleId
FROM 
(
    SELECT 
        CSSH.id, 
        CSSH.creativeScheduleId,
        rn = ROW_NUMBER() OVER (
            PARTITION BY CSSH.creativeScheduleId
            ORDER BY CSSH.id DESC)
    FROM dbo.creative_schedule_status_histories AS CSSH
) AS Q1
WHERE
    Q1.rn = 1
-- Encourage optimizer
ORDER BY
    Q1.creativeScheduleId DESC,
    Q1.id DESC;




StmtText


  |--Filter(WHERE:([Expr1001]=(1)))

       |--Sequence Project(DEFINE:([Expr1001]=row_number))

            |--Segment

                 |--Index Scan(OBJECT:([fiddle_f5403d09f7984aa197dd1242a929993a].[dbo].[creative_schedule_status_histories].[idx_creativescheduleid_id] AS [CSSH]), ORDERED BACKWARD)