求sql语句：分组中的最大值

[yl113]

type name date
1 a 15
2 b 16
1 cd 18
1 cc 19
2 ddf 19


结果要求为
type name date
1 cc 19
2 ddf 19


语句不能为 select * from (selct * from) where 这种形势

[synico]

select type_name,data from table_name where data in (select max(date) from table_name)

[black_dragon]

space6212() 的sql对是对了，不过绕了个圈，效率低了，改进一下
select type,name,date from
(select type,name,date,row_number() over (partition by type order by date desc) rn from tablename)
where rn=1;
如果表中最大值的行重复的话
select type,name,date from
(select type,name,date,max(date) over (partition by type) max_date from tablename)
where date=max_date;

[超叔csdn]

select type,first_value( name)over(partition by type order by date desc),max( date )over(partition by type)
from tablename
group by type

[yl113]

还是不行啊

[xiaoxian0411]

SELECT AVG(type),MAX(date),MAX(name) FROM table GROUP BY type

[space6212]

SQL> select * from test;

TYPE NAME MYDATE
--------------------------------------- ---------- ---------------------------------------
1 a 15
2 b 16
1 cd 18
1 cc 19
2 ddf 19

SQL> select * from test where rowid in (select rowid from (select row_number() over (partition by type order by mydate desc) n from test) where n=1)
2 ;

TYPE NAME MYDATE
--------------------------------------- ---------- ---------------------------------------
1 cc 19
2 ddf 19

[atsoftworld]

select * from test where date=(select Max(date) as date from test)
PL/SQL测试通过

[子陌红尘]

select
t.*
from
test t
where
not exists(select * from test where type=t.type and date>t.date)