select * from (select b.*, rownum rn from (select a.owner,a.table_name,a.num_rows from all_tables a where a.num_rows is not null order by a.num_rows desc)b) c where c.rn=1;
还有种写法, select * from t1 a where 最值字段=(select max(最值字段) from t1 where 分组字段=a.分组字段)这种写法可靠么?
select * from ( select * from t order by col desc|asc ) where rownum=1
select * from ( select t.*, row_number(partition by col1 order by col2 desc) rn from t) where rn=1
我想落实下 select * from t1 a where 最值字段=(select max(最值字段) from t1 where 分组字段=a.分组字段)这种写法可靠么?
1.聚合函数+group by+over(partition by ) 2.聚合函数+group by+over(over by) 3.聚合函数+group by+substr()
(select b.*, rownum rn
from (select a.owner,a.table_name,a.num_rows
from all_tables a
where a.num_rows is not null order by a.num_rows desc)b) c
where c.rn=1;
select * from t1 a where 最值字段=(select max(最值字段) from t1 where 分组字段=a.分组字段)这种写法可靠么?
select * from (
select * from t
order by col desc|asc )
where rownum=1
select * from (
select t.*, row_number(partition by col1 order by col2 desc) rn
from t)
where rn=1
select * from t1 a where 最值字段=(select max(最值字段) from t1 where 分组字段=a.分组字段)这种写法可靠么?
2.聚合函数+group by+over(over by)
3.聚合函数+group by+substr()
而且好移植