Geek Answers Handbook

How can I assign a number to each row in a table representing a record number?

How can I show the number of rows in a table so that when adding a new record, the number representing the row will be higher, and when the record is deleted, the number will be updated accordingly?
To be more clear, suppose I have a simple table:

ID int (primary key) Name varchar (5)

The identifier is set in order to receive the increase by itself (using the identification specification), therefore it cannot represent the number of lines (records), because if I have, for example, 3 records:

GO NAME
1 alex
2 Scott
3 sarah

and I will remove Alex and Scott and add a new entry:

3 Sara 4 Mina

So basically I'm looking for a sql-side solution for this so that I don't change anything else in the source code in several places.

I tried to write something to do this work, but it fails. Here he is:

 SELECT COUNT(*) AS [row number],Name FROM dbo.Test GROUP BY ID, Name HAVING (ID = ID)

It shows how:

 row number Name 1 Alex 1 Scott 1 Sara

while I want it to display as:

 row number Name 1 Alex 2 Scott 3 Sara
+6
source share
4 answers

If you just need a row number when selecting data, not in the database, you can use this

 select row_number() over(order by id) from dbo.Test

This will give line number n for the nth line.

+8
source

What you want is called auto-increment.

For SQL Server, this is achieved by adding the IDENTITY(1,1) attribute to the table definition.

Other DBMSs use a different syntax. For example, Firebird has generators that do the counting. In the BEFORE-INSERT trigger, you must assign an ID field to the current value of the generator (which will be automatically increased).

+2
source

Try

 SELECT id, name, ROW_NUMBER() OVER (ORDER BY id) AS RowNumber FROM MyTable
+2
source

I had this exact problem a while ago, but I used SQL Server 2000, so although line number () is the best solution, in SQL Server 2000 it is not available. A workaround for this is to create a temporary table, insert all values ​​with automatic increment, and replace the current table with a new table in T-SQL.

0
source

More articles:


All Articles