Click here to Skip to main content
14,239,724 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)
20 Nov 2011CPOL
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.

License

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

Share

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 --
Alternative
Tip/Trick
Posted 15 Nov 2011

Tagged as

Stats

8.9K views