How to generate auto increment field in select query

by

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

More Related Contents:

  1. SQL Server, How to set auto increment after creating a table without data loss?
  2. Reset AutoIncrement in SQL Server after Delete
  3. nvarchar concatenation / index / nvarchar(max) inexplicable behavior
  4. How do I get list of all tables in a database using TSQL?
  5. Storing images in SQL Server?
  6. Escape a string in SQL Server so that it is safe to use in LIKE expression
  7. Limitations of SQL Server Express
  8. SQL time difference between two dates result in hh:mm:ss
  9. Storing files in SQL Server
  10. Dynamic SQL (passing table name as parameter)
  11. Dapper.NET and stored proc with multiple result sets
  12. “order by newid()” – how does it work?
  13. T-SQL Dynamic SQL and Temp Tables
  14. Efficient ISNUMERIC() replacements on SQL Server?
  15. How to determine total number of open/active connections in ms sql server 2005
  16. Login to Microsoft SQL Server Error: 18456
  17. Find non-ASCII characters in varchar columns using SQL Server
  18. Tablix: Repeat header rows on each page not working – Report Builder 3.0
  19. Violation of UNIQUE KEY constraint on INSERT WHERE COUNT(*) = 0 on SQL Server 2005
  20. ORDER BY DATE showing NULLS first then most recent dates
  21. How can I determine in SQL Server if a dateTime range overlaps another
  22. How to split a string after specific character in SQL Server and update this value to specific column
  23. SQL Server Configuration Manager cannot be found
  24. Copy table to a different database on a different SQL Server
  25. Include missing months in Group By query
  26. Generate table relationship diagram from existing schema (SQL Server) [closed]
  27. How does SQL Server decide format for implicit datetime conversion?
  28. Error converting data types when importing from Excel to SQL Server 2008
  29. EAV over SQL Server
  30. The Best Way to shred XML data into SQL Server database columns

Leave a Comment