求一sql语句，加急。。。

[txgaozhao]

有一个表 news,结构如下
id type update
1 1 2009-01-01
2 1 2009-01-02
3 2 2009-01-03
4 3 2009-01-04
5 3 2009-01-05
6 3 2009-01-06
7 2 2009-01-07
8 2 2009-01-08

要获取表的每种类型中update最新的一条数据，请问如何实现？
就是要获得如下的结果
id type update
2 1 2009-01-02
8 2 2009-01-08
6 3 2009-01-06

[我姓区不姓区]

update 是datetime型的还是字符型的？

[txgaozhao]

datetime 型的

[我姓区不姓区]

select * from Table_1 t1 where [update] = 
(select max([update]) from Table_1 t2 where t2.[type]=t1.[type]) 
order by id

[txgaozhao]

update有重复的怎么办？
貌似这个ok了
select distinct type, id ,max(update) from news n1 group by type，id

[我姓区不姓区]

select * from Table_1 t1 where [update] = 
(select max([update]) from Table_1 t2 where t2.[type]=t1.[type]) 
and id=(select MAX(id) from Table_1 t3 where t3.[type]=t1.[type])
order by id

[我姓区不姓区]

[Quote=引用 4 楼 txgaozhao 的回复:]
update有重复的怎么办？
貌似这个ok了
select distinct type, id ,max(update) from news n1 group by type，id
[/Quote]
你这个可以吗？应该不可以吧

[HDNGO]

select * from news aa
where id in (select top 1 id from news where type=aa.type order by update desc)
order by aa.type

[HDNGO]

每个类根据排序取前N条。。。。

select * from news aa
where id in (select top 1 id from news where type=aa.type order by update desc)
order by aa.type

[别样苍茫]

select * from Table_1 t1 where [update] =
(select max([update]) from Table_1 t2 where t2.[type]=t1.[type])
and id=(select MAX(id) from Table_1 t3 where t3.[type]=t1.[type])
order by id

[ljhcy99]

1.
select * from table,

(select max(update) update ,type
from table
group by type) A
where a.update =table.update
and a.type=table.type
order by table.type
2,
select id, type, update,
from
(
select *,row_number()over(partition by type order by update desc) as cnt
from table) a
where cnt=1

[HDNGO]

改一个关键字加上方括号~~

select * from news aa
where id in (select top 1 id from news where type=aa.type order by [update] desc)
order by aa.type

[HDNGO]

declare @news table(
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[type] [int] NOT NULL ,
	[update] [datetime] NOT NULL 
)

insert into @news
select 1,'2009-1-6'
union select 2,'2009-2-4'
union select 3,'2009-2-2'
union select 2,'2009-2-3'
union select 1,'2009-2-1'
union select 2,'2009-1-4'
union select 3,'2009-1-5'

select * from @news aa
where id in (select top 1 id from @news where type=aa.type order by [update] desc)
order by aa.type

/*结果
2	1	2009-02-01 00:00:00.000
5	2	2009-02-04 00:00:00.000
7	3	2009-02-02 00:00:00.000
*/

[HDNGO]

如果时间有重复。。。觉得应该ID倒序，大的应该是后加的吧。。。

select * from @news aa
where id in (select top 1 id from @news where type=aa.type order by [update] desc)
order by aa.type asc,aa.id desc

[hongmaohouzi]

select * from news aa
where id in (select top 1 id from news where type=aa.type order by update desc)
order by aa.type

[hack8]

答案很多呀，给出一个思路
select top + order by + group by ,不愿意写出全部代码了，对成长不利。

[chengqscjh]

if object_id('news') is not null drop table news
create table news (ID int identity(1,1),[type] int,[update] datetime)
insert into news
select 1,'2009-01-01' union all
select 1,'2009-01-02' union all
select 2 ,'2009-01-03' union all
select 3 ,'2009-01-04' union all
select 3,'2009-01-05' union all
select 3 ,'2009-01-06' union all
select 2 ,'2009-01-07' union all
select 2 ,'2009-01-08' 


select * from news aa
where ID in (select top 1 ID from news where [type]=aa.[type] order by [update] desc)
order by aa.[type]

[飞飞虫2015]

我是来顶帖的

[wndsc]

学习一下

[amandag]

才看到题目就这么多答案了..

[woshidaniu]

好像用“select top 1* from table_name order by date desc"可以把，我试过了，ok