Effective query for latest SQL record

Question

I need to query a table for the latest record for all available dates (end of daytime). The example below illustrates what I am trying to achieve.

My question is that the most efficient way to accomplish this task is to create a table (primary key, etc.) and query LEFT OUTER JOIN.

CREATE TABLE [PriceHistory]
(
    [RowID] [int] IDENTITY(1,1) NOT NULL,
    [ItemIdentifier] [varchar](10) NOT NULL,
    [EffectiveDate] [date] NOT NULL,
    [Price] [decimal](12, 2) NOT NULL,

    CONSTRAINT [PK_PriceHistory] 
       PRIMARY KEY CLUSTERED ([ItemIdentifier] ASC, [RowID] DESC, [EffectiveDate] ASC)
)

INSERT INTO [PriceHistory] VALUES ('ABC','2016-03-15',5.50)
INSERT INTO [PriceHistory] VALUES ('ABC','2016-03-16',5.75)
INSERT INTO [PriceHistory] VALUES ('ABC','2016-03-16',6.25)
INSERT INTO [PriceHistory] VALUES ('ABC','2016-03-17',6.05)
INSERT INTO [PriceHistory] VALUES ('ABC','2016-03-18',6.85)
GO

SELECT 
    L.EffectiveDate, L.Price
FROM 
    [PriceHistory] L
LEFT OUTER JOIN 
    [PriceHistory] R ON L.ItemIdentifier = R.ItemIdentifier 
                     AND L.EffectiveDate = R.EffectiveDate 
                     AND L.RowID < R.RowID
WHERE 
    L.ItemIdentifier = 'ABC' and R.EffectiveDate is NULL
ORDER BY 
    L.EffectiveDate

Follow up: ItemIdentifiers, . . , . @MRID = Max(RowID) . , "ABC" "2016-03-16" ​​ , @MRID , .

Answer 1

@SeanLange , :

with sortedResults as
(
    select *
        , ROW_NUMBER() over(PARTITION by ItemIdentifier, EffectiveDate  
                            ORDER by ID desc) as RowNum
    from PriceHistory
)

select ItemIdentifier, EffectiveDate, Price
from sortedResults
where RowNum = 1
order by 2

Answer 2

, 1 ItemIdentifier. , . - , ItemIdentifier.

with sortedResults as
(
    select *
        , ROW_NUMBER() over(PARTITION by ItemIdentifier order by EffectiveDate desc) as RowNum
    from PriceHistory
)
select *
from sortedResults
where RowNum = 1

Answer 3

The short answer is no.

You fall into the same table twice and, possibly, create a scan with a cyclic table depending on the existing indexes. In the best case, you do a loop index search and then throw away most of the rows.

This will be the most effective request for what you ask.

SELECT
    L.EffectiveDate,
    L.Price
FROM
    (
        SELECT
            L.EffectiveDate,
            L.Price,
            ROW_NUMBER() OVER (
                PARTITION BY 
                    L.ItemIdentifier, 
                    L.EffectiveDate
                ORDER BY RowID DESC ) RowNum
        FROM [PriceHistory] L
        WHERE L.ItemIdentifier = 'ABC'
    ) L
WHERE
    L.RowNum = 1;