如果存在其他的列也有不同的重复的话 SELECT * FROM T A WHERE NOT EXISTS(SELECT 1 FROM T WHERE userid=A.userid and n<A.n)
select * from t where not exists(select 1 from t a where a.id=t.id and a.name>t.name)
select * from ( select *,ROW_NUMBER(partition by userid order by 排序字段) rank1 from 表 ) A where rank1=1
select * from t where not exists(select 1 from t a where a.userid=t.userid and a.name>t.name) ======================================== http://www.dbtuning.cn 主营:中小企业数据库管理、优化、调校服务 ========================================
declare @t table(userid varchar(10),code varchar(10),name varchar(10)) insert into @t select '0001','0002','AAAA' insert into @t select '0001','0002','BBBB' insert into @t select '0002','0001','CCCC' insert into @t select '0002','0002','DDDD' insert into @t select '0003','0001','EEEE' insert into @t select '0004','0002','AAAA'select t.* from @t t where not exists(select 1 from @t where userid=t.userid and name<t.name)/* userid code name ---------- ---------- ---------- 0001 0002 AAAA 0002 0001 CCCC 0003 0001 EEEE 0004 0002 AAAA */
select * from table, (select userid max(列) from table group by userid ) as b where table.userid=b.userid and table.列=b.列
SELECT * FROM T A
WHERE NOT EXISTS(SELECT 1 FROM T WHERE userid=A.userid and n<A.n)
(
select *,ROW_NUMBER(partition by userid order by 排序字段) rank1 from 表
) A where rank1=1
========================================
http://www.dbtuning.cn
主营:中小企业数据库管理、优化、调校服务
========================================
insert into @t select '0001','0002','AAAA'
insert into @t select '0001','0002','BBBB'
insert into @t select '0002','0001','CCCC'
insert into @t select '0002','0002','DDDD'
insert into @t select '0003','0001','EEEE'
insert into @t select '0004','0002','AAAA'select t.* from @t t where not exists(select 1 from @t where userid=t.userid and name<t.name)/*
userid code name
---------- ---------- ----------
0001 0002 AAAA
0002 0001 CCCC
0003 0001 EEEE
0004 0002 AAAA
*/
select * from table,
(select userid max(列) from table group by userid ) as b
where table.userid=b.userid
and table.列=b.列