Use over partition in subquery : Over « Analytical Functions « Oracle PL / SQL






Use over partition in subquery

  

SQL> create table myTable
  2  as
  3  select rownum id, a.*
  4    from all_objects a
  5   where 1=0
  6  /

Table created.

SQL>
SQL>
SQL> select owner, last_ddl_time, object_name, object_type
  2  from ( select myTable1.*,max(last_ddl_time) over (partition by owner) max_time from myTable myTable1)
  3  where last_ddl_time = max_time
  4  /

no rows selected

SQL>
SQL>
SQL> drop table myTable;

Table dropped.

   
    
  








Related examples in the same category

1.Include a final ordering of the result set with an ORDER BY at the end of the query
2.sum over (nothing)
3.sum over partition by, order by
4.Get Products: RANK() OVER in subquery
5.Lag salary over, lead salary over
6.Using Analytic Functions AVG(Mark) OVER (PARTITION BY StudentID ORDER BY StudentID, Mark)
7.Using Analytic Functions: AVG(Mark) OVER
8.Using Analytic Functions: AVG(Mark) OVER (ORDER BY StudentID, Mark)
9.Using Analytic Functions: AVG(Mark) OVER(PARTITION BY StudentID ORDER BY StudentID, Mark RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
10.Using Analytic Functions: AVG(Mark) OVER(PARTITION BY StudentID ORDER BY StudentID, Mark ROWS 1 preceding