Fonction SQL Row_Number() dans la clause Where

Question

J'ai trouvé une réponse à une question avec le Row_Number() dans la clause where. Lorsque j'ai essayé une requête, j'ai obtenu l'erreur suivante :

"Msg 4108, Niveau 15, Etat 1, Ligne 1 Les fonctions fenêtrées ne peuvent apparaître que dans les clauses SELECT ou ORDER BY".

Voici la requête que j'ai essayée. Si quelqu'un sait comment résoudre ce problème, faites-le moi savoir.

SELECT employee_id 
FROM V_EMPLOYEE 
WHERE row_number() OVER ( ORDER BY employee_id ) > 0 
ORDER BY Employee_ID

Answer 1

J'ai l'impression que toutes les réponses montrant l'utilisation d'un CTE ou d'une sous-requête sont des solutions suffisantes pour cela, mais je ne vois personne aller au cœur de la raison pour laquelle le PO a un problème. La raison pour laquelle ce que le PO suggère ne fonctionne pas est due à l'ordre logique de traitement des requêtes :

1. DE
2. ON
3. JOIN
4. OÙ
5. GROUPE PAR
6. AVEC CUBE/ROLLUP
7. AYANT
8. SÉLECTIONNER
9. DISTINCT
10. ORDER BY
11. TOP
12. OFFSET/FETCH

Je pense que cela contribue grandement à la réponse, car cela explique pourquoi des problèmes comme celui-ci se produisent. WHERE est toujours traité avant SELECT rendant un CTE ou une sous-requête nécessaire pour de nombreuses fonctions. Vous verrez cela souvent dans SQL Server.

Answer 2

WITH MyCte AS 
(
    select 
       employee_id,
       RowNum = row_number() OVER (order by employee_id)
    from V_EMPLOYEE 
)
SELECT  employee_id
FROM    MyCte
WHERE   RowNum > 0
ORDER BY employee_id

Answer 3

Utilisation de CTE (SQL Server 2005+) :

WITH employee_rows AS (
  SELECT t.employee_id,
         ROW_NUMBER() OVER ( ORDER BY t.employee_id ) 'rownum'
    FROM V_EMPLOYEE t)
SELECT er.employee_id
  FROM employee_rows er
 WHERE er.rownum > 1

Utilisation de l'affichage en ligne/Alternative non équivalente au CTE :

SELECT er.employee_id
  FROM (SELECT t.employee_id,
               ROW_NUMBER() OVER ( ORDER BY t.employee_id ) 'rownum'
          FROM V_EMPLOYEE t) er
 WHERE er.rownum > 1

Answer 4

Basé sur la réponse de l'OP à la question :

Veuillez consulter ce lien. Il a une solution différente, qui semble fonctionne pour la personne qui a posé la question. J'essaie de trouver une solution comme celle-ci.

Requête paginée utilisant le tri sur différentes colonnes en utilisant ROW_NUMBER() OVER () en SQL Server 2005

~Joseph

La "méthode 1" ressemble à la requête du PO dans la question liée, et la "méthode 2" ressemble à la requête dans la réponse sélectionnée. Vous deviez regarder le code lié à cette question. réponse pour voir ce qui se passait réellement, puisque le code de la réponse choisie a été modifié pour que cela fonctionne. Essayez ceci :

DECLARE @YourTable table (RowID int not null primary key identity, Value1 int, Value2 int, value3 int)
SET NOCOUNT ON
INSERT INTO @YourTable VALUES (1,1,1)
INSERT INTO @YourTable VALUES (1,1,2)
INSERT INTO @YourTable VALUES (1,1,3)
INSERT INTO @YourTable VALUES (1,2,1)
INSERT INTO @YourTable VALUES (1,2,2)
INSERT INTO @YourTable VALUES (1,2,3)
INSERT INTO @YourTable VALUES (1,3,1)
INSERT INTO @YourTable VALUES (1,3,2)
INSERT INTO @YourTable VALUES (1,3,3)
INSERT INTO @YourTable VALUES (2,1,1)
INSERT INTO @YourTable VALUES (2,1,2)
INSERT INTO @YourTable VALUES (2,1,3)
INSERT INTO @YourTable VALUES (2,2,1)
INSERT INTO @YourTable VALUES (2,2,2)
INSERT INTO @YourTable VALUES (2,2,3)
INSERT INTO @YourTable VALUES (2,3,1)
INSERT INTO @YourTable VALUES (2,3,2)
INSERT INTO @YourTable VALUES (2,3,3)
INSERT INTO @YourTable VALUES (3,1,1)
INSERT INTO @YourTable VALUES (3,1,2)
INSERT INTO @YourTable VALUES (3,1,3)
INSERT INTO @YourTable VALUES (3,2,1)
INSERT INTO @YourTable VALUES (3,2,2)
INSERT INTO @YourTable VALUES (3,2,3)
INSERT INTO @YourTable VALUES (3,3,1)
INSERT INTO @YourTable VALUES (3,3,2)
INSERT INTO @YourTable VALUES (3,3,3)
SET NOCOUNT OFF

DECLARE @PageNumber     int
DECLARE @PageSize       int
DECLARE @SortBy         int

SET @PageNumber=3
SET @PageSize=5
SET @SortBy=1

--SELECT * FROM @YourTable

--Method 1
;WITH PaginatedYourTable AS (
SELECT
    RowID,Value1,Value2,Value3
        ,CASE @SortBy
             WHEN  1 THEN ROW_NUMBER() OVER (ORDER BY Value1 ASC)
             WHEN  2 THEN ROW_NUMBER() OVER (ORDER BY Value2 ASC)
             WHEN  3 THEN ROW_NUMBER() OVER (ORDER BY Value3 ASC)
             WHEN -1 THEN ROW_NUMBER() OVER (ORDER BY Value1 DESC)
             WHEN -2 THEN ROW_NUMBER() OVER (ORDER BY Value2 DESC)
             WHEN -3 THEN ROW_NUMBER() OVER (ORDER BY Value3 DESC)
         END AS RowNumber
    FROM @YourTable
    --WHERE
)
SELECT
    RowID,Value1,Value2,Value3,RowNumber
        ,@PageNumber AS PageNumber, @PageSize AS PageSize, @SortBy AS SortBy
    FROM PaginatedYourTable
    WHERE RowNumber>=(@PageNumber-1)*@PageSize AND RowNumber<=(@PageNumber*@PageSize)-1
    ORDER BY RowNumber

--------------------------------------------
--Method 2
;WITH PaginatedYourTable AS (
SELECT
    RowID,Value1,Value2,Value3
        ,ROW_NUMBER() OVER
         (
             ORDER BY
                 CASE @SortBy
                     WHEN  1 THEN Value1
                     WHEN  2 THEN Value2
                     WHEN  3 THEN Value3
                 END ASC
                ,CASE @SortBy
                     WHEN -1 THEN Value1
                     WHEN -2 THEN Value2
                     WHEN -3 THEN Value3
                 END DESC
         ) RowNumber
    FROM @YourTable
    --WHERE  more conditions here
)
SELECT
    RowID,Value1,Value2,Value3,RowNumber
        ,@PageNumber AS PageNumber, @PageSize AS PageSize, @SortBy AS SortBy
    FROM PaginatedYourTable
    WHERE 
        RowNumber>=(@PageNumber-1)*@PageSize AND RowNumber<=(@PageNumber*@PageSize)-1
        --AND more conditions here
    ORDER BY
        CASE @SortBy
            WHEN  1 THEN Value1
            WHEN  2 THEN Value2
            WHEN  3 THEN Value3
        END ASC
       ,CASE @SortBy
            WHEN -1 THEN Value1
            WHEN -2 THEN Value2
            WHEN -3 THEN Value3
        END DESC

SORTIE :

RowID  Value1 Value2 Value3 RowNumber  PageNumber  PageSize    SortBy
------ ------ ------ ------ ---------- ----------- ----------- -----------
10     2      1      1      10         3           5           1
11     2      1      2      11         3           5           1
12     2      1      3      12         3           5           1
13     2      2      1      13         3           5           1
14     2      2      2      14         3           5           1

(5 row(s) affected

RowID  Value1 Value2 Value3 RowNumber  PageNumber  PageSize    SortBy
------ ------ ------ ------ ---------- ----------- ----------- -----------
10     2      1      1      10         3           5           1
11     2      1      2      11         3           5           1
12     2      1      3      12         3           5           1
13     2      2      1      13         3           5           1
14     2      2      2      14         3           5           1

(5 row(s) affected)

Answer 5

Veuillez consulter ce lien. Il propose une solution différente, qui semble fonctionner pour la personne qui a posé la question. J'essaie de trouver une solution comme celle-ci.

http://stackoverflow.com/questions/230058/paginated-query-using-sorting-on-different-columns-using-rownumber-over-in

~Joseph