CODEWITHSUNDEEP
sqloracle-database
Thinker
Thinker

Reputation: 6892

How can I create a temporary sequence column in my sql query result?

I have a table which looks like this.

NAME    AGE
james   22
ames    12
messi   32
....
....

I can query this table using Select name, age from emp;

Now what I want is having an extra column before name which will be 1,2,3..N if the query return n rows.

SEQUENCE    NAME    AGE
1    james  22
2    ames   12
3    messi  32
4    ....
    ....

How I can do this?

Upvotes: 2

Views: 9614

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27251

if you want to just add a column which will contain sequence number at display time(not actually store that data in a table) you can use ROWNUM pseudocolumn or row_number() analytical function.

select row_number() over(order by name) seq
     , name
     , age
  from your_table

      SEQ NAME               AGE
---------- ----------- ----------
         1 ames                12
         2 james               22
         3 messi               32

The output of the above query is ordered by NAME but you can order by any column or combination of columns you want.

Second approach is using rownum pseudocolumn. result is ordered by name also

 select rownum seq
      , name
      , age
   from ( select name
               , age
           from your_table
          order by name
         )



       SEQ NAME               AGE
---------- ----------- ----------
         1 ames                12
         2 james               22
         3 messi               32

Upvotes: 2

heximal
heximal

Reputation: 10517

you can try

Select ROWNUM sequence, name , age from emp;

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

Upvotes: 1

Related Questions