Selecting Top N Records on the basis of Group in SQL

In this article, i am going to explain how to select Top N Records from each group in SQL. First you have to create two temporary table like this:-

CREATE TABLE [#Producttable](
    [ProductName] [NVARCHAR](50) NOT NULL,
    [ProductID] [INT] NOT NULL
) 

INSERT [#Producttable] ([ProductName], [ProductID]) VALUES ('abc', 707)
INSERT [#Producttable] ([ProductName], [ProductID]) VALUES ('bcd', 708)
INSERT [#Producttable] ([ProductName], [ProductID]) VALUES ('cde', 709)
INSERT [#Producttable] ([ProductName], [ProductID]) VALUES ('def', 710)
INSERT [#Producttable] ([ProductName], [ProductID]) VALUES ('fgh', 711)



CREATE TABLE [#Saleordertable](
    [SalesOrderID] [INT] NOT NULL,
    [ProductID] [INT] NOT NULL
) 
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71782, 707)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71783, 707)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71784, 707)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71782, 708)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71783, 708)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71784, 708)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71782, 711)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71783, 711)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71784, 711)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71782, 712)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71783, 712)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71784, 712)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71782, 714)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71783, 714)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71784, 714)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71782, 715)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71783, 715)
INSERT [#Saleordertable] ([SalesOrderID], [ProductID]) VALUES (71784, 715)

Now we want to select only one salesorderID for each product which exist in '[#Producttable]' table. We have to do something like this:-

    SELECT 
         p.[ProductName], sod.SalesOrderID,
        sod.ProductID
    FROM [#Saleordertable] sod inner join [#Producttable] as p
    on sod.ProductID = p.ProductID
    WHERE sod.SalesOrderID IN
    (
        SELECT TOP 1 SalesOrderID
        FROM [#Saleordertable]
        WHERE ProductID = sod.ProductID
        ORDER BY SalesOrderID
    )

Output

That’s it!!…..Happy Programming...