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

Question

How can I show the number of rows in a table in a way that when a new record is added the number representing the row goes higher and when a record is deleted the number gets updated accordingly?
To be more clear,suppose I have a simple table like this :

ID int (primary key) Name varchar(5)

The ID is set to get incremented by itself (using identity specification) so it can't represent the number of row(record) since if I have for example 3 records as:

ID NAME
1 Alex
2 Scott
3 Sara

and I delete Alex and Scott and add a new record it will be:

3 Sara
4 Mina

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

I tried to write something to get the job done but it failes. Here it is :

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

This shows as:

row number            Name
1                     Alex
1                     Scott
1                     Sara

while I want it to get shown as:

row number            Name
1                     Alex
2                     Scott
3                     Sara

i updated the question.I want to show the numbers when i use select commands.so that each time i select the results they have a normal Acending numbering representing records. — Hossein, Feb 17 '13 at 18:27
Cant you just show id generated in the row number column? Didnt understood what is use of count(*) — DevelopmentIsMyPassion, Feb 17 '13 at 18:38
Does this help: http://stackoverflow.com/questions/1079480/how-to-determine-position-of-row-in-sql-result-set/1079602#1079602 — Bob Mc, Feb 17 '13 at 18:41

score 10 · Accepted Answer · answered Feb 17 '13 at 18:43

10

If you just want the number against the rows while selecting the data and not in the database then you can use this

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

This will give the row number n for nth row.

answered Feb 17 '13 at 18:43

Saksham

9,037
7
45
73

why do i get "The OVER SQL construct or statement is not supported." error ? (got it, i shouldnt run this in the designer :) ) – Hossein Feb 17 '13 at 18:48

score 3 · Answer 2 · edited Jul 04 '17 at 17:21

3

Try

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

edited Jul 04 '17 at 17:21

Hossein

24,202
35
119
224

answered Feb 17 '13 at 18:46

DevelopmentIsMyPassion

3,541
4
34
60

score 2 · Answer 3 · answered Feb 17 '13 at 18:27

2

What you want is called an auto increment.

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

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

answered Feb 17 '13 at 18:27

alzaimar

4,572
1
16
30

I know that and i am using it now, the problem with Identity(1,1) is that if i have records aleardy and delete those (suppose ID number 1) and start again i will not get that number 1 as ID again even when there is no records at all !. if i have 3 records as (1 2 3 , and delete number two , they will as (1 3 ) i want to get them to 'show' as ( 1 2 ) – Hossein Feb 17 '13 at 18:29
Why would you want that? Ok, suppose you want it, then use a trigger. And a second table which holds the deleted IDs. – alzaimar Feb 17 '13 at 20:12
How possibly could i do that?apart form that is it even more reasonable that using row_number() in this scenario? – Hossein Feb 18 '13 at 05:04
Hi, I misunderstood your problem. You want to *show* row numbers and not maintain them in the table. Doing that would drastically increase performance overhead as with each delete, you would have to renumber the table. It is generally not a good idea to have the RDBMS issue row numbers, as this also implies an ordering function. – alzaimar Feb 18 '13 at 07:28
Thanks , so what could be a feasible solution in this regard then? – Hossein Feb 18 '13 at 09:47
Use any of the RowNumber() OVER ... solutions they create a view of your data the way you want it. – alzaimar Feb 18 '13 at 20:31
Brilliant answer - if you can not use order by for some reason, this is definitely the solution. – FrenkyB Sep 18 '17 at 06:38

score 0 · Answer 4 · answered Feb 17 '13 at 20:20

I had this exact problem a while ago, but I was using SQL Server 2000, so although row number() is the best solution, in SQL Server 2000, this isn't available. A workaround for this is to create a temporary table, insert all the values with auto increment, and replace the current table with the new table in T-SQL.

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

4 Answers4