Click here to Skip to main content
Click here to Skip to main content
Alternative Tip/Trick

Tagged as

Selecting parent records with specific subset of child records

, 20 Nov 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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
Architect Gowi
Serbia Serbia
Started as a young scientist - winning the highest national awards in mathematics, physics, electrotechnics, and electronics.
Graduated from Faculty of Electrical Engineering, Department of Computer Techniques and Informatics, University of Belgrade, Serbia, as a first in the class, as a Master of Software Sciences.
Currently working in Gowi as a Software engineer, architect, and project manager since 2004 - mostly using Microsoft technologies (ASP.NET, C#). Member of JQuery community - created few popular plugins (four popular JQuery DataTables add-ins and loadJSON template engine).
Interests: Software engineering process(estimation and standardization), mobile and business intelligence platforms.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 20 Nov 2011
Article Copyright 2011 by Jovan Popovic
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid