SQL SERVER 2000这个语句怎么写！

[未来少年]

高手们请问个问题:先查询最大的日期，但如果日期相同就查询最大的ID，这句怎么写？
ID ROWID datetime
2 001 2013-06-01 09:12:31
119 001 2013-06-01 09:12:31
526 001 2013-05-21 11:33:17
查询结果
119 001 2013-06-01 09:12:31

[-Tracy-McGrady-]

select * from tb where id=( select max(id) from tb where datetime =( select max(datetime) from tb ))

[發糞塗牆]

---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-08-07 13:11:27 -- Version: -- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) -- Jun 10 2013 20:09:10 -- Copyright (c) Microsoft Corporation -- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据：[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([ID] int,[ROWID] varchar(3),[datetime] datetime) insert [huang] select 2,'001','2013-06-01 09:12:31' union all select 119,'001','2013-06-01 09:12:31' union all select 526,'001','2013-05-21 11:33:17' --------------开始查询-------------------------- SELECT * FROM huang WHERE id IN ( SELECT MAX(id) FROM huang a WHERE EXISTS (SELECT 1 FROM ( select MAX([datetime]) [datetime] from [huang])b WHERE a.[datetime]=b.[datetime])) ----------------结果---------------------------- /* ID ROWID datetime ----------- ----- ----------------------- 119 001 2013-06-01 09:12:31.000 */

[-Tracy-McGrady-]

if object_id('[tb]') is not null drop table [huang]
go 
create table [tb]([ID] int,[ROWID] varchar(3),[datetime] datetime)
insert [tb]
select 2,'001','2013-06-01 09:12:31' union all
select 119,'001','2013-06-01 09:12:31' union all
select 526,'001','2013-05-21 11:33:17'

select * from tb where id=(
  select max(id) from tb where datetime =(
    select max(datetime) from tb
))

drop table tb

------------
ID          ROWID datetime
----------- ----- -----------------------
119         001   2013-06-01 09:12:31.000

(1 行受影响)

[叶子]

declare @T table([ID] int,[ROWID] varchar(3),[datetime] datetime)
insert @T
select 2,'001','2013-06-01 09:12:31' union all
select 119,'001','2013-06-01 09:12:31' union all
select 526,'001','2013-05-21 11:33:17'

;WITH maco AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY rowid ORDER BY [datetime] DESC ,id desc) AS rid, * from @T
)
SELECT [ID],[ROWID],[datetime] FROM maco WHERE rid=1
/*
ID          ROWID datetime
----------- ----- -----------------------
119         001   2013-06-01 09:12:31.000
*/

[chuifengde]

DECLARE @a TABLE(ID INT,ROWID CHAR(3),d DATETIME )
INSERT @a SELECT 2 ,'001','2013-06-01 09:12:31'
UNION ALL SELECT 119 ,'001' ,'2013-06-01 09:12:31'
UNION ALL SELECT 526 ,'001' ,'2013-05-21 11:33:17'


SELECT * FROM @a a 
WHERE NOT EXISTS(SELECT 1 FROM @a WHERE ROWID=a.ROWID 
	AND CONVERT(VARCHAR(20),d, 120)+RIGHT(10000+id,4)> CONVERT(VARCHAR(20),a.d, 120)+RIGHT(10000+a.id,4))
--result
/*ID          ROWID d                                                      
----------- ----- ------------------------------------------------------ 
119         001   2013-06-01 09:12:31.000

（所影响的行数为 1 行）
*/