Click here to Skip to main content
12,299,715 members (56,009 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

6.8K views
Posted

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
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.

You may also be interested in...

Comments and Discussions

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