Como selecionar em uma tabela sem incluir valores de coluna repetidos?

SQLfiddle

SELECT H.*
FROM History AS H
OUTER APPLY
(
    SELECT TOP (1)
        H2.Price
    FROM History AS H2
    WHERE
        H2.Product = H.Product
        AND H2.Kind = H.Kind
        AND H2.Effective < H.Effective
    ORDER BY
        H2.Effective DESC
) AS X
WHERE
    NOT EXISTS (SELECT X.Price INTERSECT SELECT H.Price);

Este é um bom plano de execução para o pequeno número de linhas fornecido na pergunta. Para uma tabela maior, o índice ideal para esta consulta é:

CREATE UNIQUE INDEX [dbo.History Product, Kind, Effective]
ON dbo.History (Product, Kind, Effective DESC)
INCLUDE (Price);

Esse índice é essencialmente as chaves de índice agrupadas em uma ordem mais útil. Dependendo de como a tabela é usada para outras consultas, pode ser melhor substituir o índice clusterizado em vez de criar esse novo índice.

Não use porcentagens de custo estimado em lote para comparar consultas diferentes. Esta não é uma comparação válida em geral. Os custos são sempre estimativas do otimizador e não devem ser usados ​​dessa forma. Verifique as métricas de desempenho reais (tempo decorrido, E/S, uso de CPU, uso de memória) de todas as maneiras, mas não confie nas porcentagens.

Se esta consulta ou aquela baseada ROW_NUMBERé mais eficiente depende da distribuição dos dados e de outros fatores. Cada um tem suas vantagens e desvantagens. Essa consulta tem vantagens quando há muitas linhas para cada combinação (produto, tipo).

SELECT  *
FROM   (SELECT TOP 1 *
        FROM    History
        ORDER BY Effective ASC) AS f

UNION ALL

SELECT  *
FROM    History AS a
WHERE   a.Price <> (SELECT TOP 1 Price
                    FROM    History AS b
                    WHERE   b.Effective < a.Effective 
                    ORDER BY Effective DESC) 

ORDER BY Effective ASC ;

Teste no SQL-Fiddle

Obrigado a @JonSeigel por me direcionar para Query (duração) diferente entre alteração de valor de dados . Estou postando uma versão modificada de sua resposta aqui porque meu problema era um pouco diferente (necessário PARTITION BYno ROW_NUMBER), então talvez alguém ache a variante útil. Se você achar esta resposta útil, vote na resposta de Jon, porque eu não teria chegado a isso sem ela.

Em meus testes de tempo bastante básicos em uma tabela contendo ~ 3 milhões de registros, esta solução é cerca de 3 vezes mais rápida que a resposta de @AK_ .

Esta é a consulta:

WITH a AS
(
  SELECT
    History.*,
    ROW_NUMBER() OVER (PARTITION BY Product, Kind ORDER BY Effective ASC) AS RN
  FROM History
)
SELECT
  a1.Effective,
  a1.Product,
  a1.Kind,
  a1.Price
FROM a a1
LEFT OUTER JOIN a a2
  ON a2.RN = a1.RN - 1
    AND a2.Product = a1.Product
    AND a2.Kind = a1.Kind
WHERE
  (a1.Price <> a2.Price)
  OR (a2.RN IS NULL)

Para um número maior de linhas:

As classificações podem ser eliminadas usando o índice:

CREATE UNIQUE INDEX [dbo.History Product, Kind, Effective]
ON dbo.History (Product, Kind, Effective)
INCLUDE (Price);

O que é necessário é uma solução LEAD/LAG implementada em 2008. Infelizmente, ela não foi disponibilizada até 2012. Encontrei algumas implementações realmente boas no SQLAuthority . Uma comparação pode ser feita entre o LEAD e o valor atual e a linha retornada onde eles são diferentes.

Para Sql Server 2012 usando Lag ()

;with h as(
SELECT  Effective,
    Product,
    Kind,
    Price, 
    lag (price, 1, 0) OVER (PARTITION BY product,kind order by Effective) as prevprice
FROM History )
select Effective, Product, Kind, Price
 from h where (prevprice <> price or prevprice = 0.00)