Usuário com melhor resposta
Distinct com order by ??

Pergunta
-
A consulta abaixo esta gerando o seguinte erro:
Msg 145, Level 15, State 1, Line 56
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.Alguem sabe como resolver ? Segue a consulta :
DECLARE @Total INT
SELECT @Total = Count(DISTINCT idproduct)
FROM (SELECT idproduct,
price,
name,
preco_promocional,
datacadastro,
maisvendidos,
realquantity,
Row_number()
OVER (
ORDER BY CASE WHEN Patindex('%óleo% % côco%', name) = 0 THEN
9999
ELSE
Patindex('%óleo% % côco%', name) END, CASE WHEN Patindex(
'%côco% % óleo%',
name) = 0 THEN 9999 ELSE Patindex('%côco% % óleo%', name) END,
CASE
WHEN
Patindex('%côco% óleo%', name) = 0 THEN 9999 ELSE Patindex(
'%côco% óleo%',
name) END, CASE WHEN Patindex('%côco% óleo%', name) = 0 THEN
9999 ELSE
Patindex('%côco% óleo%', name) END, CASE WHEN Patindex(
'%óleo% côco%',
name) =
0 THEN 9999 ELSE Patindex('%óleo% côco%', name) END, CASE WHEN
Patindex(
'%óleo% côco%', name) = 0 THEN 9999 ELSE Patindex(
'%óleo% côco%', name
) END)AS
RowNumber
FROM vproductsbusca
WHERE idsite IN ( 69, 75, 76 )
AND ( busdescricao COLLATE sql_latin1_general_cp1_ci_ai LIKE
'%óleo%côco%'
OR busdescricao COLLATE sql_latin1_general_cp1_ci_ai LIKE
'%côco%óleo%'
OR busdescricao COLLATE sql_latin1_general_cp1_ci_ai LIKE
'%côco%óleo%'
OR busdescricao COLLATE sql_latin1_general_cp1_ci_ai LIKE
'%côco%óleo%'
OR busdescricao COLLATE sql_latin1_general_cp1_ci_ai LIKE
'%óleo%côco%'
OR busdescricao COLLATE sql_latin1_general_cp1_ci_ai LIKE
'%óleo%côco%' )
GROUP BY idproduct,
price,
name,
preco_promocional,
datacadastro,
maisvendidos,
realquantity) AS vProductsPaging
SELECT DISTINCT idproduct,
price,
name,
preco_promocional,
datacadastro,
maisvendidos,
realquantity,
CASE
WHEN Patindex('%óleo% % côco%', name) = 0 THEN 9999
ELSE Patindex('%óleo% % côco%', name)
END,
CASE
WHEN Patindex('%côco% % óleo%', name) = 0 THEN 9999
ELSE Patindex('%côco% % óleo%', name)
END,
CASE
WHEN Patindex('%côco% óleo%', name) = 0 THEN 9999
ELSE Patindex('%côco% óleo%', name)
END,
CASE
WHEN Patindex('%côco% óleo%', name) = 0 THEN 9999
ELSE Patindex('%côco% óleo%', name)
END,
CASE
WHEN Patindex('%óleo% côco%', name) = 0 THEN 9999
ELSE Patindex('%óleo% côco%', name)
END,
CASE
WHEN Patindex('%óleo% côco%', name) = 0 THEN 9999
ELSE Patindex('%óleo% côco%', name)
END
FROM (SELECT idproduct,
price,
name,
preco_promocional,
datacadastro,
maisvendidos,
realquantity,
Row_number()
OVER (
ORDER BY CASE WHEN Patindex('%óleo% % côco%', name) = 0 THEN
9999
ELSE
Patindex('%óleo% % côco%', name) END, CASE WHEN Patindex(
'%côco% % óleo%',
name) = 0 THEN 9999 ELSE Patindex('%côco% % óleo%', name) END,
CASE
WHEN
Patindex('%côco% óleo%', name) = 0 THEN 9999 ELSE Patindex(
'%côco% óleo%',
name) END, CASE WHEN Patindex('%côco% óleo%', name) = 0 THEN
9999 ELSE
Patindex('%côco% óleo%', name) END, CASE WHEN Patindex(
'%óleo% côco%',
name) =
0 THEN 9999 ELSE Patindex('%óleo% côco%', name) END, CASE WHEN
Patindex(
'%óleo% côco%', name) = 0 THEN 9999 ELSE Patindex(
'%óleo% côco%', name
) END)AS
RowNumber
FROM vproductsbusca
WHERE idsite IN ( 69, 75, 76 )
AND ( busdescricao COLLATE sql_latin1_general_cp1_ci_ai LIKE
'%óleo%côco%'
OR busdescricao COLLATE sql_latin1_general_cp1_ci_ai LIKE
'%côco%óleo%'
OR busdescricao COLLATE sql_latin1_general_cp1_ci_ai LIKE
'%côco%óleo%'
OR busdescricao COLLATE sql_latin1_general_cp1_ci_ai LIKE
'%côco%óleo%'
OR busdescricao COLLATE sql_latin1_general_cp1_ci_ai LIKE
'%óleo%côco%'
OR busdescricao COLLATE sql_latin1_general_cp1_ci_ai LIKE
'%óleo%côco%' )
GROUP BY idproduct,
price,
name,
preco_promocional,
datacadastro,
maisvendidos,
realquantity) AS vProductsPaging
WHERE rownumber BETWEEN 1 AND 20
ORDER BY CASE
WHEN Patindex('%óleo% % côco%', name) = 0 THEN 9999
ELSE Patindex('%óleo% % côco%', name)
END,
CASE
WHEN Patindex('%côco% % óleo%', name) = 0 THEN 9999
ELSE Patindex('%côco% % óleo%', name)
END,
CASE
WHEN Patindex('%côco% óleo%', name) = 0 THEN 9999
ELSE Patindex('%côco% óleo%', name)
END,
CASE
WHEN Patindex('%côco% óleo%', name) = 0 THEN 9999
ELSE Patindex('%côco% óleo%', name)
END,
CASE
WHEN Patindex('%óleo% côco%', name) = 0 THEN 9999
ELSE Patindex('%óleo% côco%', name)
END,
CASE
WHEN Patindex('%óleo% côco%', name) = 0 THEN 9999
ELSE Patindex('%óleo% côco%', name)
END
Respostas
-
- Marcado como Resposta Phillipe F. Santos sexta-feira, 15 de junho de 2012 17:48
Todas as Respostas
-
- Marcado como Resposta Phillipe F. Santos sexta-feira, 15 de junho de 2012 17:48
-