Arranging row numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Arranging row numbers

Author

Topic

bissa
Starting Member

32 Posts

Posted - 2012-05-08 : 16:20:16

I have some rows in the table not arranged correctly
Example:
8000 name
7987 name
7986 name
8005 name
8003 name
You will see row numbers are a mess which can cause some delay sometimes.
Is there a query to arrange the rows that they appear in ascending way 1,2,3... instead of creating random numbers?

Thank you

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-08 : 16:24:31

Select * from youtable order by col1 

Note: Col1 one is which contains your data like 8000,7987,7986..so on

bissa
Starting Member

32 Posts

Posted - 2012-05-08 : 16:28:02

This query show the values I need but only as a result, how I can implement it to the main table?

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-08 : 16:33:05

quote:
Originally posted by bissa

This query show the values I need but only as a result, how I can implement it to the main table?


Check this example:
create table #a( a int)

insert into #a values(2)

insert into #a values(1)

insert into #a values(3)

insert into #a values(4)

select * into #b from #a order by a

select * from #b

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 16:47:41

quote:
Originally posted by bissa

This query show the values I need but only as a result, how I can implement it to the main table?

the best way to do this is to create row number field as an identity field so that values will get autogenerated in sequence

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bissa
Starting Member

32 Posts

Posted - 2012-05-08 : 21:28:29

How I can create row number field as an identity field while re-creating the table?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-08 : 21:41:32

Why does it matter how the record is stored in the table ?

As shown by vijays3, you use ORDER BY to specify the sequence of how the record returns to you.

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 21:57:17

quote:
Originally posted by bissa

How I can create row number field as an identity field while re-creating the table?

why do you need to re-create the table? You want even to change existing id values also to be sequential?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bissa
Starting Member

32 Posts

Posted - 2012-05-08 : 22:14:04

I thought I need to re-create the table so ID get auto generated, ID's are not arragned correctly.
Order By show me the correct order but I want the table to be shown same arrangement without the need to use Order By.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 22:16:22

quote:
Originally posted by bissa

I thought I need to re-create the table so ID get auto generated, ID's are not arragned correctly.
Order By show me the correct order but I want the table to be shown same arrangement without the need to use Order By.

there's no concept of order for sql table. For guaranteed order you need to use ORDER BY as suggested before

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources