Use rank and dense_rank in subquery : DENSE_RANK « Analytical Functions « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Analytical Functions
  3. DENSE_RANK

Use rank and dense_rank in subquery

  

SQL> CREATE TABLE sales(
  2    product_id            NUMBER(6),
  3    cid           NUMBER,
  4    time_id               DATE,
  5    sold         NUMBER(3),
  6    amount                NUMBER(10,2),
  7    cost                  NUMBER(10,2)
  8  );

Table created.

SQL>
SQL> select * from
  2    (select product_id,
  3           sum(sold),
  4           rank () over (order by sum(sold) desc) as rank,
  5           dense_rank () over (order by sum(sold) desc) as dense_rank
  6    from sales
  7    where to_char(time_id, 'yyyy-mm') = '2001-06'
  8    group by product_id)
  9  where rank < 11;

no rows selected

SQL>
SQL>
SQL> drop table sales;

Table dropped.








Related examples in the same category

1.DENSE_RANK()rank items, leaves no gaps when there is a tie
2.Deal with Null in Rank
3.Deal with Null in dense_rank
4.DENSE_RANK() with NULLS FIRST
5.DENSE_RANK() with NULLS LAST
6.DENSE_RANK function
7.Use DENSE_RANK() to get the top rank
8.Compare rank() and dense_rank()
9.dense_rank() over (order by comm desc nulls last)
10.decode dense_rank
11.dense_rank() over ( partition by deptno order by sal desc )
12.rank and dense_rank with group by