SQL分组查询，并查出未分组列的第一行
例如Staff表有ID, Name, Date, Dype
001 aaa 2013-01-01 a
001 aaa 2013-01-02 b
002 bbb 2013-01-01 a
002 bbb 2013-01-02 a

怎样能查出这样记录？，如下：
001 aaa 2013-01-01 a
002 bbb 2013-01-01 a
这个是按ID,Name分组查询，Date，Dype列显示对应第一行记录？
[解决办法]

WITH    cte
          AS ( SELECT   row_number() OVER ( PARTITION BY ID, Name ORDER BY Date ) AS xh ,
                        *
               FROM     Staff
             )
    SELECT  ID,      Name,   Date,       Dype
    FROM    cte
    WHERE   xh = 1 ;

[解决办法]

select ID,Name,Date,Dype from Staff where exists (select min(id) from Staff  group by Name)

[解决办法]

引用:

忘记了，是SQL 2005，没有row_number()吧

05开始有的row_number()函数，也可以用通用的子查询
select * from tb a
where not exists
(select 1 from tb where id=a.id and Date<a.Date)