Click here to Skip to main content
14,237,394 members

Selecting parent records with specific subset of child records

Rate this:
0.00 (No votes)
Please Sign up or sign in to vote.
0.00 (No votes)
15 Nov 2011CPOL
I will show you how you can select one parent row with two latest child rows using T-SQL

Imagine this kind of SQL query - you have a parent/child tables such as Product/ProductItem, Company/Employee, Article/Categories where parent and child tables are connected with one-to-many relationship. Let we assume that you have following Company/Employee tables with the following structure:



Each company can have several employees which are connected via CompanyId foreign key. Also, date when employee is hired is placed in the Employee table


How can you create SQL query that returns parent row and exactly two child rows? examples of such kind of queries are:
1. List all companies and employee who has been hired first
2. List all companies and last two hired employees
3. List all companies with two employees with the highest salaries

Example of result for the query number 2 would be:

Companies | Date Hired | Employee
Company1 | 17/11/2011 | John Spenser
Company1 | 16/11/2011 | Mike Johnson
Company2 | 10/11/2011 | Jasmin Fleet
Company2 | 05/11/2011 | George Lee
Company3 | 14/11/2011 | John Do
Company3 | 13/11/2011 | Adam Johnson
Company4 | 06/11/2011 | Adelle Spenser
Company4 | 28/10/2011 | Stephen Murphy


You will need to join Company and Employee tables and partition the results by CompanyId, in order to group results by companies.
Then, you will need order employees by date hired (or salary - this is specific for your problem) and find order index with this condition.
In the final query you will filter results by order index (e.g. if you need last two employees you will put order_index < 2 in the WHERE clause)

In the following listing is shown that kind of code:

WITH company_employees (CompanyName, EmployeeName, DateHired, order_index) AS (
SELECT	Company.CompanyName,
				ORDER BY Employee.DateHired desc)
	FROM         Company
	INNER JOIN   Employee
		ON Employee.CompanyId = Company.CompanyId
SELECT * FROM company_employees
WHERE order_index IN ( 1,2)

In this query I have user common table expression (neater replacement for sub-query) where I have joined parent and child table.
ROW_NUMBER function returns me index of the employee partitioned by companies ordered in the by the date hired i descending order)
In the main query I have filtered only those records that have order_index 1 or 2 because I need to find only last two hired employees in each company.

If I have placed ascending order in the ROW_NUMBER, two employees that are hired first would be returned. instead of the DateHired I could use any other field such as salary.

With minor change you can also use this query if you have similar requirement.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Jovan Popovic(MSFT)
Program Manager Microsoft
Serbia Serbia
Graduated from Faculty of Electrical Engineering, Department of Computer Techniques and Informatics, University of Belgrade, Serbia.
Currently working in Microsoft as Program Manager on SQL Server product.
Member of JQuery community - created few popular plugins (four popular JQuery DataTables add-ins and loadJSON template engine).
Interests: Web and databases, Software engineering process(estimation and standardization), mobile and business intelligence platforms.

Comments and Discussions

-- There are no messages in this forum --
Posted 15 Nov 2011

Tagged as


3 bookmarked