如何取每组最新的一条数据？

[li_zhifu]

id name groupid lastdatetime
1 aaa 001 2007-03-06
2 bbb 001 2007-03-07
3 ccc 001 2007-03-05
4 ddd 002 2007-03-05
5 eee 002 2007-03-08

--------------------------------------------
想得到这样的结果
id name groupid lastdatetime
2 bbb 001 2007-03-07
5 eee 002 2007-03-08

[青锋-SS]

select tb1.* from tb1,(select groupid,max(lastdatetime) from tb1) t where tb1.groupid=t.groupid and tb1.lastdatetime=t.lastdatetime;

[wangdehao]

select * from tb a
inner join
(select groupid ,max(lastdatetime) lastdatetime from tb group by groupid )b
on a.groupid =b.groupid and a.lastdatetime=b.lastdatetime

[子陌红尘]

declare @t table(id int,name varchar(10),groupid varchar(4),astdatetime varchar(20))
insert into @t select 1,'aaa','001','2007-03-06'
insert into @t select 2,'bbb','001','2007-03-07'
insert into @t select 3,'ccc','001','2007-03-05'
insert into @t select 4,'ddd','002','2007-03-05'
insert into @t select 5,'eee','002','2007-03-08'


select t.* from @t t where t.id=(select max(id) from @t where groupid=t.groupid)

select t.* from @t t where not exists(select 1 from @t where groupid=t.groupid and id>t.id)

select a.* from @t a,(select groupid,max(id) as id from @t group by groupid) b where a.id=b.id and a.groupid=b.groupid

[paoluo]

也寫下


Select *
From TableName A
Where lastdatetime = (Select Max(lastdatetime) From TableName Where name = A.name)

[paoluo]

libin_ftsafe(子陌红尘：当libin告别ftsafe),你的寫法有問題的，id和lastdatetime沒有絕對的關係的。

應該改為

select t.* from @t t where t.lastdatetime=(select max(lastdatetime) from @t where groupid=t.groupid)

select t.* from @t t where not exists(select 1 from @t where groupid=t.groupid and lastdatetime>t.lastdatetime)

select a.* from @t a,(select groupid,max(lastdatetime) as lastdatetime from @t group by groupid) b where a.lastdatetime=b.lastdatetime and a.groupid=b.groupid

[mengmou]

不写了，顶

[dawugui]

if object_id('pubs..tb') is not null
drop table tb
go

create table tb
(
id int,
name varchar(10),
groupid varchar(10),
lastdatetime datetime
)

insert into tb(id,name,groupid,lastdatetime) values(1, 'aaa', '001', '2007-03-06')
insert into tb(id,name,groupid,lastdatetime) values(2, 'bbb', '001', '2007-03-07')
insert into tb(id,name,groupid,lastdatetime) values(3, 'ccc', '001', '2007-03-05')
insert into tb(id,name,groupid,lastdatetime) values(4, 'ddd', '002', '2007-03-05')
insert into tb(id,name,groupid,lastdatetime) values(5, 'eee', '002', '2007-03-08')

select a.* from tb a,
(select groupid , max(lastdatetime) as lastdatetime from tb group by groupid) b
where a.groupid = b.groupid and a.lastdatetime = b.lastdatetime

drop table tb

id name groupid lastdatetime
----------- ---------- ---------- ------------------------------------------------------
5 eee 002 2007-03-08 00:00:00.000
2 bbb 001 2007-03-07 00:00:00.000

（所影响的行数为 2 行）

[shoulley]

select * from tb a
where not exists(select * from tb where groupid=a.groupid and lastdatetime>a.lastdatetime)
正解!!

[li_zhifu]

已搞定，原来Max(LastDateTime)也是有效的，汗

[l932]

select * from 表名 where id = (select id from 表 where lastdatetime = (select max(lastdatetime) from 表 where groupid = 1))

[Sevence_Wiser]

select id from tabelname as ta,
(select GroupID,max(lastupdatedate) as lastupdatedate from tabelname group by GroupID) as tb
where ta.GroupID = tb.GroupID and ta.lastupdatedate = tb.lastupdatedate

[sanyii31]

SELECT *
FROM 表
WHERE EXISTS(
SELECT *
FROM (
SELECT GroupId,max(LastDateTime) maxLastDateTime
FROM 表
GROUP BY GroupId
)maxt
WHERE 表.GroupId=maxt.GroupId
AND 表.LastDateTime=maxt.maxLastDateTime
)

[guichuan]

select * from TableName where id in (select max(ID) from TableName group by GroupId)

[guichuan]

看错啦

[zyj1219304]

select * from tableName where id in(select max(lastdatetime) from tableName group by groupId);

[dragon_sea]

上面是对的！顶！

[砸死牛顿的苹果]

select * from tablename a where a.id in (select top 1 b.id from tablename b where a.groupid=b.groupid order by lastdatetime desc) order by a.groupid

[wyf2]

我也来写一个：
select b.id,b.name,a.groupid,a.lastdatetime
from ( select groupid,max(lastdatetime)lastdatetime
from myexample group by groupid) a join myexample b on a.lastdatetime= b.lastdatetime

[cooljiji]

libin_ftsafe(子陌红尘：当libin告别ftsafe) ( ) 信誉：105 Blog 2007-3-9 10:48:58 得分: 10 第一个错了.为什么会给他分啊?

[wyf2]

对了，问下
select * from tb a
where not exists(select * from tb where groupid=a.groupid and lastdatetime>a.lastdatetime)
lastdatetime>a.lastdatetime这个有什么区别，不懂