none
Distinct com order by ?? RRS feed

  • 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 

    sexta-feira, 15 de junho de 2012 16:57

Respostas

Todas as Respostas