求sql command-每item 最top 1之資料

[kw_725]

recid item date
1 a 20/12/2013
2 a 19/12/2013
3 a 23/12/2013
4 b 21/12/2013
5 b 19/12/2013
6 b 20/12/2013

答案要:是每item 最top 1之資料
3 a 23/12/2013
4 b 21/12/2013

[熊猫王子]

我写个通用的吧

select * from t1 inner join (select MAX(DATE) as date1,item from t1 group by item) t2
on t1.date=t2.date1 and t1.item=t2.item

[LongRui888]

适合2005及以上的版本：

select recid,	item	,date
from 
(
select *,
       ROW_NUMBER() over(partition by item order by date desc) rownum
from 表
)t
where rownum = 1

[xxfvba]

看起来我只能写个2000的了 select * from 表 a where not exists (select * from 表 b where a.item=b.item and a.date<b.date)

[Mr_Nice]

if object_id('[TB]') is not null drop table [TB]
create table [TB]([recid] int,[item] varchar(1),[date] varchar(10))
insert [TB]
select 1,'a','20/12/2013' union all
select 2,'a','19/12/2013' union all
select 3,'a','23/12/2013' union all
select 4,'b','21/12/2013' union all
select 5,'b','19/12/2013' union all
select 6,'b','20/12/2013'

select * from [TB]


SELECT DISTINCT c.*
FROM dbo.TB A
CROSS APPLY(SELECT TOP (1) recid,item,DATE FROM TB B WHERE A.item=b.item ORDER BY date DESC) AS C

/*
recid	item	DATE
3	a	23/12/2013
4	b	21/12/2013*/

[lzw_0736]

WITH a1 (recid,item,date) AS
(
SELECT 1,'a','20/12/2013' UNION all
SELECT 2,'a','19/12/2013' UNION all
SELECT 3,'a','23/12/2013' UNION all
SELECT 4,'b','21/12/2013' UNION all
SELECT 5,'b','19/12/2013' UNION all
SELECT 6,'b','20/12/2013'
)
select a.*
from a1 a
CROSS APPLY
(
select TOP 1 recid from a1 WHERE item=a.item ORDER BY date desc
) b
where a.recid=b.recid

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-01-09 12:43:02
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([recid] int,[item] varchar(1),[date] varchar(10))
insert [huang]
select 1,'a','20/12/2013' union all
select 2,'a','19/12/2013' union all
select 3,'a','23/12/2013' union all
select 4,'b','21/12/2013' union all
select 5,'b','19/12/2013' union all
select 6,'b','20/12/2013'
--------------开始查询--------------------------

select * from [huang] a
WHERE EXISTS (SELECT 1 FROM (SELECT item,MAX([date])[date] FROM huang GROUP BY item) b WHERE a.item=b.item AND a.[date]=b.[date])

----------------结果----------------------------
/* 
recid       item date
----------- ---- ----------
3           a    23/12/2013
4           b    21/12/2013
*/