CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
Prajakta Kalghatgi
Prajakta Kalghatgi

Reputation: 45

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

Upvotes: 1

Views: 12725

Answers (4)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Your code actually looks to me to be MySQL, not Postgres code. I do get your error running on Postgres, but Postgres typically does not allow user variables to be used in regular SQL queries anyway. When running your query against MySQL, I get an error that the id column does not exist.

But, in any case, Postgres has supported the ROW_NUMBER analytic function for a long time, so just use that instead:

SELECT
    Name,
    Student_ID,
    ROW_NUMBER() OVER (ORDER BY Student_ID) Serial
FROM table1
ORDER BY
    Student_ID;

Upvotes: 0

Michael Buen
Michael Buen

Reputation: 39393

use row_number, it works on Postgres

select *, row_number() over(order by id) as serial from table1

Your code @n := @n + 1 is MySQL's work-around for its lack of windowing functions (e.g., row_number) on its older versions. The latest MySQL now has windowing functions. You can just merely use row_number, no need for work-around.

And you don't even need that work-around in Postgres. It has windowing functions long before almost everyone else

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

use row_number()

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

Upvotes: 1

Rima
Rima

Reputation: 1455

try with row_number()

SELECT row_number()over(order by id) n,
           name, 
           id
      FROM table1 
     ORDER BY id

Upvotes: 0

Related Questions