CODEWITHSUNDEEP
oracle-databaseoracle12c
Venkata Ramireddy CH
Venkata Ramireddy CH

Reputation: 753

row number based on unique column value in oralce?

I do need to implement a oracle sql to have row number defined as below:

row_num, column1, column2, cloumn3
1, ABC, 123, a1
1, ABC, 125, a2
2, ABD, 123, a3
2, ABD, 124, a4
2, ABD, 125, a5
3, ABE, 123, a1

Here I defined row number based on unique value of column1.

Can any one help me to write oracle sql to define row number in this way?

Thanks Venkat

Upvotes: 0

Views: 901

Answers (2)

Boneist
Boneist

Reputation: 23588

You need the DENSE_RANK() analytic function:

WITH your_table AS (SELECT 'ABC' col1, 123 col2, 'a1' col3 FROM dual UNION ALL
                    SELECT 'ABC' col1, 125 col2, 'a2' col3 FROM dual UNION ALL
                    SELECT 'ABD' col1, 123 col2, 'a3' col3 FROM dual UNION ALL
                    SELECT 'ABD' col1, 124 col2, 'a4' col3 FROM dual UNION ALL
                    SELECT 'ABD' col1, 125 col2, 'a5' col3 FROM dual UNION ALL
                    SELECT 'ABE' col1, 123 col2, 'a1' col3 FROM dual)
  -- end of subquery mimicking your_table with data in; See SQL below:
SELECT dense_rank() OVER (ORDER BY col1) row_num,
       col1,
       col2,
       col3
FROM   your_table
ORDER BY col1, col2, col3;

   ROW_NUM COL1       COL2 COL3
---------- ---- ---------- ----
         1 ABC         123 a1
         1 ABC         125 a2
         2 ABD         123 a3
         2 ABD         124 a4
         2 ABD         125 a5
         3 ABE         123 a1

DENSE_RANK() is similar to RANK() in that it will assign tied rows the same rank (that is, rows that have the same values in the columns being ordered - in your case, col1), but unlike RANK(), DENSE_RANK() won't skip rank numbers.

Upvotes: 2

Utsav
Utsav

Reputation: 8103

You can use this as well, but I forgot about dense rank. So Boneist's answer is better.

select rn.row_num , t.* from 
(select column1,row_number() over (order by column1) as row_num from 
(select distinct column1 column1 from tbl23) )rn
inner join tbl23 t
on rn.column1=t.column1

Upvotes: 1

Related Questions