CODEWITHSUNDEEP
sqloracle-database
Jimmy
Jimmy

Reputation: 11

Adding a column to show derived row number while selecting

If I am selecting data from tables how can I append a new column with respect to column in my table?

Foe example, I am getting data from table as

select cont_no,Cont_name from emp;

cont_no     cont_name
abc         abc1
cde         cde1
cde         cde2
cde         cde3
efg         efg1
efg         efg2
hij         hij

but I want to add a virtual column like this:

added_column   cont_no     cont_name
     1         abc         abc1
     2         cde         cde1
               cde         cde2
               cde         cde3
     3         efg         efg1
               efg         efg2
     4         hij         hij

Upvotes: 1

Views: 50

Answers (1)

MT0
MT0

Reputation: 167982

WITH emp ( cont_no, cont_name ) AS (
  SELECT 'abc', 'abc1' FROM DUAL UNION ALL
  SELECT 'cde', 'cde1' FROM DUAL UNION ALL
  SELECT 'cde', 'cde2' FROM DUAL UNION ALL
  SELECT 'cde', 'cde3' FROM DUAL UNION ALL
  SELECT 'efg', 'efg1' FROM DUAL UNION ALL
  SELECT 'efg', 'efg2' FROM DUAL UNION ALL
  SELECT 'hij', 'hij' FROM DUAL
)
SELECT CASE cont_no
         WHEN LAG( cont_no ) OVER ( ORDER BY cont_no, cont_name )
         THEN NULL
         ELSE DENSE_RANK() OVER ( ORDER BY cont_no )
         END as added_column,
       cont_no,
       cont_name
FROM   emp;

Output:

ADDED_COLUMN CONT_NO CONT_NAME
------------ ------- ---------
           1 abc     abc1      
           2 cde     cde1      
             cde     cde2      
             cde     cde3      
           3 efg     efg1      
             efg     efg2      
           4 hij     hij

Upvotes: 2

Related Questions