Search code examples
sqlpostgresqlwindow-functions

auto increment field in select query statement

I have a table with 2 columns, name and id with these following values

Name Student_ID jack 231
dave 425
mike 001
lily 128
liam 358

I would like to add a new column to this result with auto incremented sequence values.

I have tried using the below query. But i get a error as described below

Query:

    SELECT @n := @n +1 n,
           name, 
           id
      FROM table1, (SELECT @n := 0) m
     ORDER BY id

    Error: 
    ERROR:  syntax error at or near ":="
    LINE 2: SELECT @n := @n +1 n,

Expected Result:

    Name  Student_ID  Serial
     jack      231      1    
     dave      425      2
     mike      001      3  
     lily      128      4 
     liam      358      5
Solution

  • use row_number()

    select name, student_id, row_number() over(order by student_id) as serial
from tablename