请教这个查询语句怎么写?
idnumber status lasttime
a 0 2007-08-10
a 1 2007-08-11
a 0 2007-08-12
a 0 2007-08-13

b 1 2007-08-11
b 0 2007-08-12
b 1 2007-08-13

现在我要查询出 status 为 '0 ' 得最后一条记录
即结果为
idnumber status lasttime
a 0 2007-08-13
b 0 2007-08-12

这个该怎么写,先谢谢大家!

[解决办法]
select idnumber,status,max(lasttime) as lasttime
from tablename
where status = '0 '
group by idnumber,status
[解决办法]
---也可以试试这种,不过得不到你b那条记录
Select * From TableName A Where Status= '0 ' And Not Exists
(Select 1 From TableName Where idnumber=A.idnumber And lasttime> A.lasttime)
[解决办法]
Select * From TableName A Where A.Status= '0 ' And Not Exists
(Select 1 From TableName Where idnumber=A.idnumber And lasttime> A.lasttime and A.Status== Status )
这样就可以得到了
[解决办法]
----方法1:
select * from table as a where a.status = 0 and
not exists(select 1 from table where idnumber = a.idnumber and lasttime > a.lasttime)
----方法2:
select * from table as a where a.status = 0 and
a.lasttime = (select top 1 lasttime from table where status = 0 and idnumber = a.idnumber order by lasttime DESC)
----方法3:
select * from table as a where a.status = 0 and
a.lasttime = (select max(lasttime) from table where status = 0 and idnumber = a.idnumber group by idnumber)

[解决办法]
-- Create Table tblTest

CREATE TABLE tblTest(

idnumber CHAR(1),

status TINYINT,

lasttime SMALLDATETIME

)

GO

--Insert Test RecordSet

INSERT INTO tblTest SELECT 'a ', 0, '2007-08-10 ' UNION ALL

SELECT 'a ', 1, '2007-08-11 ' UNION ALL

SELECT 'a ', 0, '2007-08-12 ' UNION ALL

SELECT 'a ', 0, '2007-08-13 ' UNION ALL

SELECT 'b ', 1, '2007-08-11 ' UNION ALL

SELECT 'b ', 0, '2007-08-12 ' UNION ALL

SELECT 'b ', 1, '2007-08-13 '

GO

--Query The Record

SELECT idnumber, status, max(lasttime) AS lasttime

FROM tblTest

GROUP BY idnumber, status HAVING status = 0

--Query Result

idnumber status lasttime

a 0 2007-08-13

b 0 2007-08-12

(2 行受影响)