求一个想了N天也没有想出来的语句啊

显示全部楼层 · 2006-5-4 12:00:00

表的结构如下
+----+--------+------------+------------------+
| ID |GUID|ITEM| CREATETIME |
+----+--------+------------+------------------+
|1 | A|TEST1 |2006-5-4 12:00:00 |
+----+--------+------------+------------------+
|2 | A|TEST2 |2006-7-4 12:00:00 |
+----+--------+------------+------------------+
|3 | A|TEST1 |2006-5-4 14:01:00 |
+----+--------+------------+------------------+
|4 | A|TEST5 |2006-5-4 15:10:00 |
+----+--------+------------+------------------+
|5 | B|TEST2 |2006-5-4 12:00:00 |
+----+--------+------------+------------------+
|6 | B|TEST1 |2006-7-4 12:10:00 |
+----+--------+------------+------------------+
|7 | C|TEST1 |2006-5-4 12:00:00 |
+----+--------+------------+------------------+
|8 | C|TEST5 |2006-5-6 12:50:00 |
+----+--------+------------+------------------+
|9 | E|TEST3 |2006-5-3 19:01:00 |
+----+--------+------------+------------------+
我想按GUID分组求出每一个GUID在最早的时间的那条记录，我想不出来怎么用一条语句实现，所以我分的两步做的，先按GROUP BY GUID 查出GUID存到临时表，然后循环求出每个GUID的MIN(CREATETIME)最小时间更新到临时，然后再按临时表中的GUID 和 CREATETIME循环，再从表中每一个GUID在最早的时间的那条记录。
好麻烦啊~~~！
望高手指点迷津。

千问 · 2006-5-4 12:00:00

if guid + createtime is unique,
select * from a where (guid||createtime) in (
select guid||min(createtime) from a group by guid)
else
can not do this.

千问 · 2006-5-4 12:00:00

已经解决了，用的连接查询，谢谢回复