CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10granking
CompEng
CompEng

Reputation: 7376

How can I use rank function to solve this?

I have this data:

enter image description here

and I want to this result:

enter image description here

So I ve tried this:

  SELECT  
      text_area,
      ordered_area,
      RANK () OVER (ORDER BY text_area) ranked_area
       FROM
      (  
      SELECT  '1200 AA' text_area ,1 ordered_area  
       FROM
      DUAL
      UNION ALL
      SELECT  '1200 AA'  ,2 
       FROM
      DUAL
      UNION ALL
      SELECT  '1200 BB'  ,3 
       FROM
      DUAL 
      UNION ALL
      SELECT  '1200 CC'  ,4 
       FROM
      DUAL 
      UNION ALL
      SELECT  '1200 CC'  ,5 
       FROM
      DUAL  
      )

but it did not solve my problem. How can I solve this issue?

thanks in advance

Upvotes: 1

Views: 45

Answers (2)

Mureinik
Mureinik

Reputation: 311723

If you want to avoid "gaps" in your ranking, you should use dense_rank instead of rank:

DENSE_RANK () OVER (ORDER BY text_area) ranked_area

Upvotes: 1

q4za4
q4za4

Reputation: 652

DENSE_RANK not RANK:

SELECT  
      text_area,
      ordered_area,
      DENSE_RANK () OVER (ORDER BY text_area) ranked_area
       FROM
      (  
      SELECT  '1200 AA' text_area ,1 ordered_area  
       FROM
      DUAL
      UNION ALL
      SELECT  '1200 AA'  ,2 
       FROM
      DUAL
      UNION ALL
      SELECT  '1200 BB'  ,3 
       FROM
      DUAL 
      UNION ALL
      SELECT  '1200 CC'  ,4 
       FROM
      DUAL 
      UNION ALL
      SELECT  '1200 CC'  ,5 
       FROM
      DUAL  
      )

Upvotes: 3

Related Questions