Add a column with row number in select result

Asked

Viewed 1,241 times

2

How can I add the row number of the select result in an additional column?

For example:

select e.ds_estados  
  from estados e

This select would return me the registered states

    AC
    AL
    AM
    ...
    TO

I would like next to the state to return the line number together

    1  -  AC
    2  -  AC
    3  -  AC
    ...
    27  - TO
sql oracle

3 answers

2

Thank you Jeferson Almeida!

I ended up discovering another way to do this too, for those who are interested, follow the code:

select rownum linha, 
       a.estado estado 
from (select b.estado     
        from estados b) a

1

For this you can use the function row_number() follows below the example of how to use it, in case I did ordering by column e.ds_estados

select  row_number() over (order by e.ds_estados) linha, e.ds_estados  
from estados e

1

You can do it like this:

SELECT @i:=@i+1 AS position, ds_estados FROM estados, (SELECT @i:=0) AS t WHERE 1

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.