Select top Nth record from each group in SQL Server

lejogeorge
 
on Nov 18, 2022 02:07 AM
200 Views

I have 2 employees 5 record in transaction table, i want to select only 3 records of each employee in select query.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Nov 18, 2022 02:13 AM

Hi lejogeorge,

Use ROW_NUMBER function with PARTITION BY.

Refer below query.

SQL

CREATE TABLE #Customers
(
    [CustomerID] [nvarchar](50) NOT NULL,
    [ContactName] [nvarchar](50) NOT NULL,
    [City] [nvarchar](50) NOT NULL,
    [Country] [nvarchar](50) NOT NULL
)
 
INSERT #Customers ([CustomerID], [ContactName], [City], [Country]) VALUES ('51', 'Aaron', 'Newyork', 'USA')
INSERT #Customers ([CustomerID], [ContactName], [City], [Country]) VALUES ('52', 'Angela', 'Rome', 'ITALY')
INSERT #Customers ([CustomerID], [ContactName], [City], [Country]) VALUES ('53', 'Luke', 'Ibiza', 'SPAIN')
INSERT #Customers ([CustomerID], [ContactName], [City], [Country]) VALUES ('54', 'Ramson', 'London', 'UK')
INSERT #Customers ([CustomerID], [ContactName], [City], [Country]) VALUES ('55', 'Merlin', 'Munich', 'GERMANY')
 
CREATE TABLE #Interest
(
    [Workid] [nvarchar](50) NOT NULL,
    [Hobbies] [nvarchar](50) NOT NULL
)
 
INSERT #Interest ([Workid], [Hobbies]) VALUES ('51', 'Travelling')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('51', 'Reading')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('52', 'Sleeping')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('52', 'Eating')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('53', 'Swiming')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('53 ', 'Running')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('54 ', 'Dancing')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('54', 'Driving')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('55', 'Diving')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('55', 'Hunting')
 
CREATE TABLE #Staff
(
    [Workid] [nvarchar](50) NOT NULL,
    [Age] [nvarchar](50) NOT NULL,
    [Occupation] [nvarchar](50) NOT NULL
)
 
INSERT #Staff ([Workid], [Age], [Occupation]) VALUES ('51', '56', 'Driver')
INSERT #Staff ([Workid], [Age], [Occupation]) VALUES ('52', '44', 'Pilot')
INSERT #Staff ([Workid], [Age], [Occupation]) VALUES ('53', '67', 'Teacher')
INSERT #Staff ([Workid], [Age], [Occupation]) VALUES ('54', '45', 'Doctor')
INSERT #Staff ([Workid], [Age], [Occupation]) VALUES ('55', '36', 'Nurse')
 
SELECT CustomerID, ContactName, City, Country,Age,Occupation,Hobbies
FROM
(
SELECT
    ROW_NUMBER() OVER(PARTITION BY c.CustomerID ORDER BY (c.CustomerID)) RowNo,
    c.CustomerID, c.ContactName, c.City, c.Country,s.Age,s.Occupation,i.Hobbies
FROM #Customers c
INNER JOIN #Staff s ON c.CustomerID = s.Workid
INNER JOIN #Interest i ON  c.CustomerID = i.Workid) t
WHERE t.RowNo <= 3
 
DROP TABLE #Customers
DROP TABLE #Interest
DROP TABLE #Staff

Output

CustomerID ContactName City Country Age Occupation Hobbies
51 Aaron Newyork USA 56 Driver Travelling
51 Aaron Newyork USA 56 Driver Reading
52 Angela Rome ITALY 44 Pilot Sleeping
52 Angela Rome ITALY 44 Pilot Eating
53 Luke Ibiza SPAIN 67 Teacher Swiming
53 Luke Ibiza SPAIN 67 Teacher Running
54 Ramson London UK 45 Doctor Dancing
54 Ramson London UK 45 Doctor Driving
55 Merlin Munich GERMANY 36 Nurse Diving
55 Merlin Munich GERMANY 36 Nurse Hunting