Question

sql如何取group by 分组的多条记录只取最上面的一条！

例如：表table
userid ranking username
001 C aa
001 B bb
002 A cc
如何取得：001 C aa
002 A cc
求解~~~

Answer 1

1、创建测试表，create table test_order(userid varchar2(20), ranking varchar2(20), username varchar2(20));

2、插入样例数据，

insert into test_order values('001','C','aa');

insert into test_order values('001','B','bb');

insert into test_order values('002','A','cc');

3、查询表中所有记录，select t.*, rowid from test_order t;

4、编写sql，获取所需记录，

select *

  from (select t.*,

               row_number() over(partition by userid order by ranking desc) rn

          from test_order t)

 where rn = 1

Answer 2

select userid,ranking,username from table
where userid+ranking in
(
select userid+max(ranking) from table
group by userid
)

追问

不需要取max只取第一条，数据库是SQL Server要怎么做啊？

追答

是SQL2005以上的吗？语句：
select userid,ranking,username from
(
select userid,ranking,username,ROW_NUMBER() OVER(PARTITION BY userid ORDER BY userid DESC) as num
from table
) t
where t.num = 1