Distinct com order by ??

Question

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 

Answer 1

Dê uma olhada nessa thread, veja se te ajuda.

Abraços!

---

Thiago Coelho
thiagocoelho.net
@thiagokoelho

Answer 2

Valeu !