How to generate auto increment field in select query
Sql ServerAuto IncrementSql Server Problem Overview
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.
Sql Server Solutions
Solution 1 - Sql Server
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
And for SQLServer
SELECT row_number() OVER (ORDER BY first_name, last_name) n,
first_name,
last_name
FROM table1
Solution 2 - Sql Server
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
Solution 3 - Sql Server
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
Solution 4 - Sql Server
DECLARE @id INT
SET @id = 0
UPDATE cartemp
SET @id = CarmasterID = @id + 1
GO