小虾米, a couple of solutions for your query in sql server 2005+

很好的题目，相信应该有4种解法，不过这两种最常用（起码对我来说，其它的麻烦点）。很久前面试中碰到过，人家要求5分钟，我用了半分钟，给了他们下面两个solution，当场拍板要俺了，真的应当记住的。

没有你说的demo table,就用northwind的employees table。把你的query变为找出每个country中最年轻的2个employee.


1. top n [with tie]

select employeeid,country,birthdate
from employees e1
where employeeid in
(
select top 2 employeeid from employees e2 where e1.country=e2.country
order by birthdate desc )
order by country,employeeid

2. cte && rank (or row_number())

with dobcte as
(
select employeeid, country, birthdate,rank() over (partition by country order by birthdate desc) as rk
from employees
)

select * from dobcte
where rk order by country, employeeid

result:

6 UK 1963-07-02 00:00:00.000
9 UK 1966-01-27 00:00:00.000
3 USA 1963-08-30 00:00:00.000
8 USA 1958-01-09 00:00:00.000