CODEWITHSUNDEEP
sqlselectjoin
kk1076
kk1076

Reputation: 1748

Select last 5 rows in join query SQL Server 2008

I am trying to bring the last 5 rows of the Order table based on OrderDate with the column name firstname from Customer table.

The below query displays all the values from the Order table instead of last 5 rows.

SELECT  
   A.[FirstName], B.[OrderId], B.[OrderDate], B.[TotalAmount], B.[OrderStatusId]
FROM  
   [schema].[Order] B 
OUTER APPLY
   (SELECT TOP 5  *
    FROM [schema].[Customer] A  
    WHERE B.[CustomerId] = 1 
      AND A.[CustomerId] = B.[CustomerId] 
   ORDER BY
      B.[OrderDate] DESC) A

Any mistake in my logic of using TOP and DESC ?

Upvotes: 1

Views: 628

Answers (3)

Andrey Gordeev
Andrey Gordeev

Reputation: 32499

If you want to get the last 5 rows of Order table, why do you apply TOP to Customer table?

SELECT TOP 5 A.[FirstName],B.[OrderId],B.[OrderDate],B.[TotalAmount],B.[OrderStatusId]
FROM  [schema].[Order] B 
LEFT JOIN [schema].[Customer] A ON A.[CustomerId]=B.[CustomerId] 
WHERE B.[CustomerId]=1 
ORDER BY  B.[OrderDate] DESC

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Although you can use OUTER APPLY, I think of row_number() for such queries:

SELECT  A.[FirstName],B.[OrderId],B.[OrderDate],B.[TotalAmount],B.[OrderStatusId]
FROM  [schema].[Order] B join
      (select A.*, row_number() over (partition by CustomerId order by OrderDate desc) as seqnum
       from [schema].[Customer] A
       where A.[CustomerId] = 1 -- AND A.[CustomerId]=B.[CustomerId] 
      ) A
      on A.[CustomerId] = B.[CustomerId] and seqnum <= 5;

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

;WITH MyCTE AS
(

    SELECT  A.[FirstName],
            B.[OrderId],
            B.[OrderDate],
            B.[TotalAmount],
            B.[OrderStatusId],
            ROWNUMBER() OVER (ORDER BY B.[OrderDate] DESC) AS RowNum
    FROM    [schema].[Order] B 
            OUTER APPLY
            (  
                SELECT  TOP 5  *
                FROM    [schema].[Customer] A  
                WHERE   B.[CustomerId]=1 
                        AND A.[CustomerId]=B.[CustomerId] 
                ORDER BY
                B.[OrderDate] DESC                  
            )     A
)

SELECT [FirstName],
       [OrderId],
       [OrderDate],
       [TotalAmount],
       [OrderStatusId] 
FROM   MyCTE
WHERE  RowNum <= 5

Upvotes: 1

Related Questions