SyntaxFix

[sql] MSSQL Select statement with incremental integer column... not from a table

It is ugly and performs badly, but technically this works on any table with at least one unique field AND works in SQL 2000.

SELECT (SELECT COUNT(*) FROM myTable T1 WHERE T1.UniqueField<=T2.UniqueField) as RowNum, T2.OtherField
FROM myTable T2
ORDER By T2.UniqueField

Note: If you use this approach and add a WHERE clause to the outer SELECT, you have to added it to the inner SELECT also if you want the numbers to be continuous.

Examples related to sql

• Passing multiple values for same variable in stored procedure • SQL permissions for roles • Generic XSLT Search and Replace template • Access And/Or exclusions • Pyspark: Filter dataframe based on multiple conditions • Subtracting 1 day from a timestamp date • PYODBC--Data source name not found and no default driver specified • select rows in sql with latest date for each ID repeated multiple times • ALTER TABLE DROP COLUMN failed because one or more objects access this column • Create Local SQL Server database

Examples related to sql-server

• Passing multiple values for same variable in stored procedure • SQL permissions for roles • Count the Number of Tables in a SQL Server Database • Visual Studio 2017 does not have Business Intelligence Integration Services/Projects • ALTER TABLE DROP COLUMN failed because one or more objects access this column • Create Local SQL Server database • How to create temp table using Create statement in SQL Server? • SQL Query Where Date = Today Minus 7 Days • How do I pass a list as a parameter in a stored procedure? • SQL Server date format yyyymmdd

Examples related to sql-server-2000

• SQL get the last date time record • INSERT INTO @TABLE EXEC @query with SQL Server 2000 • How to get the first and last date of the current year? • SQL select max(date) and corresponding value • SQL where datetime column equals today's date? • Is there a way to get a list of all current temporary tables in SQL Server? • Grant execute permission for a user on all stored procedures in database? • Is there a way to list open transactions on SQL Server 2000 database? • Get the time of a datetime using T-SQL? • select a value where it doesn't exist in another table

Examples related to auto-increment

• How can I avoid getting this MySQL error Incorrect column specifier for column COLUMN NAME? • How to make MySQL table primary key auto increment with some prefix • Get the new record primary key ID from MySQL insert query? • How to generate auto increment field in select query • Get current AUTO_INCREMENT value for any table • Add Auto-Increment ID to existing table? • How to AUTO_INCREMENT in db2? • How to create id with AUTO_INCREMENT on Oracle? • auto increment ID in H2 database • ERROR: permission denied for sequence cities_id_seq using Postgres

Examples related to row-number

• Trouble using ROW_NUMBER() OVER (PARTITION BY ...) • How to show row number in Access query like ROW_NUMBER in SQL • Oracle 'Partition By' and 'Row_Number' keyword • Select specific row from mysql table • MySQL - Get row number on select • ROW_NUMBER() in MySQL • How do I use ROW_NUMBER()? • Equivalent of Oracle's RowID in SQL Server • MSSQL Select statement with incremental integer column... not from a table • Why is there still a row limit in Microsoft Excel?