sql server 2008，不能在子查询中使用order by

here's my sql server 2008 stored procedure.

下面是我的sql server 2008存储过程。

ALTER PROCEDURE [dbo].[GetSharedSmoothies]
    @Page INT ,
    @Status INT ,
    @ItemPerPage INT
AS 
    BEGIN
        SET NOCOUNT ON;

        DECLARE @X INT 
        DECLARE @Y INT

        SET @X = ( @Page - 1 ) * @ItemPerPage
        SET @Y = @Page * @ItemPerPage


        SELECT  *
        FROM    ( SELECT    S.* ,
                            U.Avatar ,
                            U.Displayname ,
                            ( SELECT    COUNT(Id)
                              FROM      Vote
                              WHERE     Vote.SmoothieId = S.Id
                            ) AS Votes ,
                            ROW_NUMBER() OVER ( ORDER BY S.Id ) rownum
                  FROM      dbo.Smoothie AS S
                            INNER JOIN dbo.[User] AS U ON S.UserId = U.Id
                  WHERE     S.IsPublic = 1
                            AND S.Status = 3
                            AND S.UserId > 0
                  -- ORDER BY  S.CreatedDate DESC
                ) seq
        WHERE   seq.rownum BETWEEN @X AND @Y
        ORDER BY seq.rownum
    END

in my code, you will see I comment out the order by

在我的代码中，您将看到我将订单注释掉

 -- ORDER BY  S.CreatedDate DESC

because order by will not work in subquery. i need to show the lastest one on the top. is there a way I can use order by in my code?

因为order by不会在子查询中工作。我需要在最上面展示最后一个。在我的代码中有使用order by的方法吗?

2 个解决方案

#1

You may add S.CreatedDate within the Row_NUMBER()

你可以添加。内CreatedDate Row_NUMBER()

ROW_NUMBER() OVER (PARTITION BY S.Id ORDER BY S.CreatedDate DESC) AS RowNum

ROW_NUMBER()除以(除以S)Id命令。RowNum CreatedDate DESC)

#2

That's right. It is not allowed, because it will do nothing.

这是正确的。这是不允许的，因为它什么也不会做。

Having the latest one at the top in the subquery will do nothing to the result set using the subquery.

在子查询的顶部放置最新的查询将不会对使用子查询的结果集产生任何影响。

Add the needed column to the result set ORDER BY:

将所需的列按以下顺序添加到结果集顺序:

ORDER BY seq.CreatedDate DESC, seq.rownum

Or:

或者:

ORDER BY seq.rownum, seq.CreatedDate DESC

#1