CODEWITHSUNDEEP
sqlserial-number
Shafiq Ahmed
Shafiq Ahmed

Reputation: 57

Auto generate serial numbers in sql

table orders
+----+--------+------------+
|s.no|   id   |  ordername |
+----+--------+------------+
|  1 |   34   |    name    |      
+----+--------+------------+
|  2 |   35   |    name    |        
+----+--------+------------+
|  3 |   36   |    name    |        
+----+--------+------------+
|  4 |   37   |    name    |        
+----+--------+------------+
|  5 |   38   |    name    |       
+----+--------+------------+

write query for auto generate serial numbers and when i delete an order from sql so serial numbers should be as 1234567 in serial_number row and i used order id as auto increment

Upvotes: 2

Views: 52160

Answers (4)

HUSSAIN SABOOR
HUSSAIN SABOOR

Reputation: 1

Try using the below query, it gives you serial number without any partition. I am using MSSQL server to generate the serial number.

Select row_number() over( order by (select null)) [serialno],
id,
ordername
from orders With(nolock)

Hope this would work for you.

Upvotes: 0

DEVNAG BHARAD
DEVNAG BHARAD

Reputation: 114

select @a:=0;
SELECT  @a:=@a+1 serial_number,
    marks 
FROM      student_marks,

It works for me hope i will for for you too.

Upvotes: 0

Amit Kabadi
Amit Kabadi

Reputation: 11

FOR MYSQL USERS Based on your reasons for not wanting to use user defined variables as wanting to avoid having 2 queries, one for inializing and one to use it you could use the following:

SELECT  @a:=@a+1 serial_number, 
        marks 
FROM    student_marks,
        (SELECT @a:= 0) AS a;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can write the query in most dialects of sql as:

select row_number() over (order by id) as "s.no",
       id, ordername
from orders o

Here is syntax that works in SQL Server:

with toupdate as (
      select row_number() over (order by id) as newval,
             o.*
      from orders o
     )
update toupdate set [s.no] = newval;

Upvotes: 5

Related Questions