Selecting parent records with specific subset of child records

Jovan Popovic(MSFT)

0/5 (0 vote)

Nov 15, 2011

CPOL

10070

If you are using some alternative SQL dialect without common table expression syntax, you can use correlated sub-query as the one in the following example:SELECT * FROM( SELECT Company.CompanyName, Employee.EmployeeName, Employee.DateHired, ROW_NUMBER() OVER( PARTITION BY...

If you are using some alternative SQL dialect without common table expression syntax, you can use correlated sub-query as the one in the following example:

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

In this query, I have selected items in the sub-query with order_index. Then, in the outer query I have selected only those rows with order_index below 3.