How to generate auto increment field in select query

Question

For example I have a table with 2 columns  first name and last name with these values  Ali           Khani Elizabette    Amini Britney       Spears        I want to write a select query that generate a table like this   1     Ali           Khani 2     Elizabette    Amini 3     Britney       Spears        Thanks for your help

User · Accepted Answer

If it is MySql you can try  SELECT  n     n   1 n         first name          last name   FROM table1   SELECT  n    0  m  ORDER BY first name  last name   SQLFiddle  And for SQLServer  SELECT row number   OVER  ORDER BY first name  last name  n         first name          last name    FROM table1    SQLFiddle

User · Answer

In the case you have no natural partition value and just want an ordered number regardless of the partition you can just do a row number over a constant  in the following example i ve just used  X   Hope this helps someone  select      ROW NUMBER   OVER PARTITION BY num ORDER BY col1  as aliascol1       period next id  period name long from      select distinct col1  period name long   X  as num   from  TABLE     as x

User · Answer

here s for SQL server  Oracle  PostgreSQL which support window functions   SELECT  ROW NUMBER   OVER  ORDER BY first name  last name   Sequence no          first name          last name FROM    tableName    SQLFiddle Demo

User · Answer

DECLARE  id INT  SET  id   0  UPDATE cartemp SET  id   CarmasterID    id   1  GO

[sql-server] How to generate auto increment field in select query

Examples related to sql-server

Examples related to auto-increment