Abaixo segue uma QUERY que da para ser utilizada para várias situações.

Veja alguns exemplos de uso:

A QUERY abaixo traz um limite máximo de dez resultados agrupados por uma coluna específica, que nesse caso a coluna se chama “ColunaX”.

WITH Registros as
(
SELECT ROW_NUMBER() OVER (Partition by ColunaX order by ColunaX) AS Cont,*
FROM TBL_TESTE
)
SELECT * FROM Registros WHERE Cont <= 10

Explicando a query.

• WITH Registros as – Cria uma tabela temporária para armazenar os dados
• ROW_NUMBER() OVER (Partition by ColunaX order by ColunaX) AS Cont – Faz uma contagem em numero sequencial (1,2,3,4…) das ocorrências com valores repetidos em “ColunaX”.
• SELECT * FROM Registros WHERE Cont <= 10 – Retorna até no máximo dez para cada registro repetido

A QUERY abaixo exclui valores repetido em uma tabela.

WITH Registros as
(
SELECT ROW_NUMBER() OVER (Partition by ColunaX order by ColunaX) AS Cont,*
FROM TBL_TESTE
)
--DELETE Registros WHERE Cont > 1

Explicando a query

• WITH Registros as – Cria uma tabela temporária para armazenar os dados
• ROW_NUMBER() OVER (Partition by ColunaX order by ColunaX) AS Cont – Faz uma contagem em numero sequencial (1,2,3,4…) das ocorrências com valores repetidos em “ColunaX”.
• DELETE Registros WHERE Cont > 1 – Exclui os valores repetido mantendo somente um Registro

Retornando apenas os valores máximos ou mínimos.

Pegando como exemplo a tabela conforme imagem abaixo.

A técnica utiliza LEFT JOIN na mesma tabela com critérios de ser o mesmo artista e ter o número de execuções maior do que o registro da tabela original.

Ao final, a consulta retorna apenas os registros que não foram relacionados (valor NULL no LEFT JOIN), que são os
registros sem nenhum outro com número de execuções maior – ou seja, os que a coluna plays é na verdade MAX(plays) daquele artista.

SELECT m1.id_artista, m1.nome, m1.plays
FROM musica m1
LEFT JOIN musica m2
    ON m1.id_artista = m2.id_artista
   AND m1.plays < m2.plays
WHERE m2.id IS NULL 

Essa fica sendo, então, a forma mais eficiente de recuperar o registro com maior valor em determinada coluna, já que não é necessário nenhum tipo de subquery e nem mesmo GORUP BY. E se quiser o menor valor, basta trocar o sinal.

Com informações de Imasters