Selecting parent records with specific subset of child records

Introduction

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:

Company
+CompanyId
+CompanyName

Employee
+EmployeeId
+CompanyId
+EmployeeName
+DateHired
+Salary

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

Problem

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

Solution

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