Select top Nth record from each group in SQL Server

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