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.
