Write a query to display top 5 rows

1.     row limiting clause

2.     inline view and rownum

3.     with clause and rownum

4.     rank

5.     dense_rank

6.     row_number

1.  row limiting clause

select * from   emp order by sal desc fetch first 5 rows only;

 

2.  inline view and rownum

select * from  (select * from   emp order by sal desc)

where rownum <= 5;

 

3.  with clause and rownum

with ds as(select * from   emp order by sal desc)

select * from   emp where  rownum <= 5;

 

4.  using rank function

select * from (select emp.*,rank() over (order by sal desc) as val_rank  from emp)

where  val_rank <= 5;

 

5.  using dense rank function

select * from (select emp.*,dense_rank() over (order by sal desc) as val_rank  from emp)

where  val_rank <= 5;

 

6.  using row_number function

select * from (select emp.*,row_number() over (order by sal desc) as val_rank  from emp)

where  val_rank <= 5;