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

Question

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

Answer 1

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

Answer 2

SQL> select * from test;                                   TYPE NAME                                        MYDATE
--------------------------------------- ---------- ---------------------------------------
                                      1 a                                               15
                                      2 b                                               16
                                      1 cd                                              18
                                      1 cc                                              19
                                      2 ddf                                             19SQL> 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

Answer 3

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

Answer 4

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

Answer 5

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;

Answer 6

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