rank and dense_rank with group by : DENSE_RANK « Analytical Functions « Oracle PL / SQL

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

rank and dense_rank with group by

  

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 product_id,
  2         sum(sold),
  3         rank () over (order by sum(sold) desc) as rank,
  4         dense_rank () over (order by sum(sold) desc) as dense_rank
  5  from sales
  6  where to_char(time_id, 'yyyy-mm') = '2001-06'
  7  group by product_id;

no rows selected

SQL>
SQL> drop table sales;

Table dropped.

   
    
  

Related examples in the same category