La clause ORDER BY n'est pas valide dans les vues, les fonctions en ligne, les tables dérivées, les sous-requêtes et les expressions de tables communes.

Question

La clause ORDER BY n'est pas valide dans les vues, les fonctions en ligne, les fonctions dérivées et les fonctions de gestion de l'information. dérivées, les sous-requêtes et les expressions de tables communes, sauf si TOP, OFFSET ou FOR XML soit également spécifiée.

J'obtiens l'erreur mentionnée ci-dessus en essayant d'exécuter la requête suivante. Quelqu'un peut-il jeter un coup d'œil et me dire ce que je fais de mal ici ?

SELECT 
    * 
FROM (
    SELECT 
        Stockmain.VRNOA, 
        item.description as item_description, 
        party.name as party_name, 
        stockmain.vrdate, 
        stockdetail.qty, 
        stockdetail.rate, 
        stockdetail.amount, 
        ROW_NUMBER() OVER (ORDER BY VRDATE) AS RowNum
    FROM StockMain 
    INNER JOIN StockDetail 
        ON StockMain.stid = StockDetail.stid 
    INNER JOIN party 
        ON party.party_id = stockmain.party_id 
    INNER JOIN item 
        ON item.item_id = stockdetail.item_id 
    WHERE stockmain.etype='purchase' 
    ORDER BY VRDATE DESC
) AS MyDerivedTable
WHERE 
    MyDerivedTable.RowNum BETWEEN 1 and 5

Answer 1

Vous n'avez pas besoin d'utiliser ORDER BY dans la requête interne après WHERE car vous l'avez déjà utilisé dans ROW_NUMBER() OVER (ORDER BY VRDATE DESC) .

SELECT 
    * 
FROM (
    SELECT 
        Stockmain.VRNOA, 
        item.description as item_description, 
        party.name as party_name, 
        stockmain.vrdate, 
        stockdetail.qty, 
        stockdetail.rate, 
        stockdetail.amount, 
        ROW_NUMBER() OVER (ORDER BY VRDATE DESC) AS RowNum  --< ORDER BY
    FROM StockMain 
    INNER JOIN StockDetail 
        ON StockMain.stid = StockDetail.stid 
    INNER JOIN party 
        ON party.party_id = stockmain.party_id 
    INNER JOIN item 
        ON item.item_id = stockdetail.item_id 
    WHERE stockmain.etype='purchase' 
) AS MyDerivedTable
WHERE 
    MyDerivedTable.RowNum BETWEEN 1 and 5

Answer 2

ORDER BY column OFFSET 0 ROWS

Il est surprenant que cela fonctionne, quelle étrange fonctionnalité.

Un exemple plus important avec un CTE comme moyen de "stocker" temporairement une longue requête pour la réorganiser plus tard :

;WITH cte AS (
    SELECT .....long select statement here....
)

SELECT * FROM 
(
    SELECT * FROM 
    ( -- necessary to nest selects for union to work with where & order clauses
        SELECT * FROM cte WHERE cte.MainCol= 1 ORDER BY cte.ColX asc OFFSET 0 ROWS 
    ) first
    UNION ALL
    SELECT * FROM 
    (  
        SELECT * FROM cte WHERE cte.MainCol = 0 ORDER BY cte.ColY desc OFFSET 0 ROWS 
    ) last
) as unionized
ORDER BY unionized.MainCol desc -- all rows ordered by this one
OFFSET @pPageSize * @pPageOffset ROWS -- params from stored procedure for pagination, not relevant to example
FETCH FIRST @pPageSize ROWS ONLY -- params from stored procedure for pagination, not relevant to example

Nous obtenons donc tous les résultats classés par MainCol

Mais les résultats avec MainCol = 1 être commandé par ColX

Et les résultats avec MainCol = 0 être commandé par ColY