表结构如下
message 表
id(varchar2) receivetime(date) projectid(varchar2)
1 2009-1-1 1
2 2009-1-2 1
3 2009-1-1 2
4 2009-1-3 2现在想根据projectId分组,取出每个projectId里面receivtime的最大时间,并且返回这个表的所有字段,
例如根据以上要求,上面的结果为:
id(varchar2) receivetime(date) projectid(varchar2)
2 2009-1-2 1
4 2009-1-3 2
怎么弄呀,急急急
message 表
id(varchar2) receivetime(date) projectid(varchar2)
1 2009-1-1 1
2 2009-1-2 1
3 2009-1-1 2
4 2009-1-3 2现在想根据projectId分组,取出每个projectId里面receivtime的最大时间,并且返回这个表的所有字段,
例如根据以上要求,上面的结果为:
id(varchar2) receivetime(date) projectid(varchar2)
2 2009-1-2 1
4 2009-1-3 2
怎么弄呀,急急急
where not exists (
select receivetime from message t2 where t1.projectid = t2.projectid and t2.receivetime > t1.receivetime);
FROM
MESSAGE
WHERE (receivetime,projectid) IN
(
SELECT MAX(receivetime) MAXTIME,projectId
from message
group by projectId
);
试试可以不
请把你的描述再清晰化点
FROM
MESSAGE
WHERE ID =
(
SELECT MAX(ID),MAX(receivetime) ,projectid
from MESSAGE
group by projectid,receivetime
);