How can I get Top 2 orders for each customer-CodePudding

SQL Server 2016 or newer

How do I get top 2 most recent orders for each customer in the orders table?

Current Output

CustomerId  order_count Id          FirstName                                          City
----------- ----------- ----------- -------------------------------------------------- --------------------------------------------------
1           3           1           Rodney                                             Augusta
2           3           2           Autumn                                             Mobile

(2 rows affected)

Id          CustomerId  OrderDate               ProductName
----------- ----------- ----------------------- --------------------------------------------------
1           1           2022-04-26 10:00:00.000 RodneyProduct1
2           2           2022-04-27 11:00:00.000 Autumn Product 1
3           1           2022-04-28 09:11:42.933 RodneyProduct2
4           1           2022-04-01 09:13:13.447 RodneyProduct3
5           2           2022-04-02 09:14:13.447 Autumn Product 2
6           2           2022-04-03 09:15:13.447 Autumn Product 3

Desired Output

Id          CustomerId  OrderDate               ProductName
----------- ----------- ----------------------- --------------------------------------------------
3           1           2022-04-28 09:11:42.933 RodneyProduct2
1           1           2022-04-26 10:00:00.000 RodneyProduct1
2           2           2022-04-27 11:00:00.000 Autumn Product 1
6           2           2022-04-03 09:15:13.447 Autumn Product 3

SQL Code

CREATE TABLE #Customer(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [City] [nvarchar](50) NULL,
    CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE #Order(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CustomerId] [int] NULL,
    [OrderDate] [datetime] NULL,
    [ProductName] [nvarchar](50) NULL,
    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE #Order ADD  CONSTRAINT [DF_Order_OrderDate]  DEFAULT (getdate()) FOR [OrderDate]
GO

SET IDENTITY_INSERT #Customer ON 
GO

INSERT #Customer ([Id], [FirstName], [City]) VALUES (1, N'Rodney', N'Augusta')
GO
INSERT #Customer ([Id], [FirstName], [City]) VALUES (2, N'Autumn', N'Mobile')
GO

SET IDENTITY_INSERT #Customer OFF
GO
SET IDENTITY_INSERT #Order ON 
GO

INSERT #Order ([Id], [CustomerId], [OrderDate], [ProductName]) 
VALUES 
(1, 1, CAST(N'2022-04-26T10:00:00.000' AS DateTime), N'RodneyProduct1')
,(2, 2, CAST(N'2022-04-27T11:00:00.000' AS DateTime), N'Autumn Product 1')
,(3, 1, CAST(N'2022-04-28T09:11:42.933' AS DateTime), N'RodneyProduct2')
,(4, 1, CAST(N'2022-04-01T09:13:13.447' AS DateTime), N'RodneyProduct3')
,(5, 2, CAST(N'2022-04-02T09:14:13.447' AS DateTime), N'Autumn Product 2')
,(6, 2, CAST(N'2022-04-03T09:15:13.447' AS DateTime), N'Autumn Product 3')
--https://www.sqlservertutorial.net/sql-server-basics/sql-server-insert-multiple-rows/


SELECT * 
FROM (
    SELECT CustomerId, COUNT(CustomerId) order_count
    FROM #Order
    GROUP BY CustomerId
) o
LEFT JOIN #Customer c
    ON o.CustomerId = c.Id

SELECT * FROM #Order

@@Version

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) 
    Oct 28 2016 18:17:30 
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 19044: )

CodePudding user response：

This is my solution

SELECT *
FROM (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate desc) OrderNumber
        , *
    FROM #Order 
) EnumeratedOrders
WHERE OrderNumber <= 2

A detailed explanation about the ROW_NUMBER() function here