0
CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null
);

INSERT INTO customers VALUES(22,'W');
INSERT INTO customers VALUES(22,'W');
INSERT INTO customers VALUES(20,'Q');
INSERT INTO customers VALUES(20,'Q');
COMMIT;

Now i am trying to get different rank corresponding to my partitions by customer name

SELECT DENSE_RANK() OVER(PARTITION BY customer_name ORDER BY CUSTOMER_ID) , CUSTOMER_ID FROM CUSTOMERS;

ouput:

1   20
1   20
1   22
1   22

expected output:

1   20
1   20
2   22
2   22
Dhruv
  • 10,291
  • 18
  • 77
  • 126

1 Answers1

1

use below query 

SELECT 
DENSE_RANK() OVER(ORDER BY CUSTOMER_ID) , 
CUSTOMER_ID 
FROM CUSTOMERS;

remove partition BY by clause because if u use partition by clause it will divide the result based on name.

Noel
  • 10,152
  • 30
  • 45
  • 67
arpit
  • 11
  • 1
  • 4