Re Order ID Field - 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 2005 Forums

Transact-SQL (2005)

Re Order ID Field

Author

Topic

erics44
Starting Member

12 Posts

Posted - 2009-12-08 : 06:12:59

Hi
I have a table with an id field and i want to reorder the numbers according to a date field in the same table, so it might look like this

ID Date
1 1st January
2 10th January
3 5th January

and i want it to look like

ID Date
1 1st January
2 5th January
3 10th January

I know i could put the info into another table and then reappend it but I wondered if there was a quick and easy method, like a reorder_id function or something.

Thanks a lot

Sachin.Nand

2937 Posts

Posted - 2009-12-08 : 06:18:30

What datatype is the Date field?

PBUH

erics44
Starting Member

12 Posts

Posted - 2009-12-08 : 06:23:46

smalldatetime

Sachin.Nand

2937 Posts

Posted - 2009-12-08 : 06:26:41

So why 1st January ?

PBUH

erics44
Starting Member

12 Posts

Posted - 2009-12-08 : 06:34:30

thats not important, it could be dates numbers letters whatever

i want to renumber the id column

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-08 : 08:00:49

This?

declare @MyTable table (ID int, Date smalldatetime)
insert @MyTable
          select 1, '1 January 2009'
union all select 2, '10 January 2009'
union all select 3, '5 January 2009'

select * from @MyTable order by ID

; with t1 as (select *, row_number() over (order by Date) as Row from @MyTable)
update t1 set ID = Row

select * from @MyTable order by ID

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

erics44
Starting Member

12 Posts

Posted - 2009-12-08 : 08:08:28

yes i think that might work

canm you update an identity field?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-08 : 08:23:09

quote:
Originally posted by erics44

yes i think that might work

canm you update an identity field?

No. Where do you want to show data?
If you use front end application, do numbering there

Madhivanan

Failing to plan is Planning to fail

erics44
Starting Member

12 Posts

Posted - 2009-12-08 : 08:34:15

I cant, thats the problem

there is a dispute at work as to who owns the front end, and the guy who wrote it wont give up the code

so i have to do database fudges until a new front end is built

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-08 : 08:41:24

quote:
Originally posted by erics44

I cant, thats the problem

there is a dispute at work as to who owns the front end, and the guy who wrote it wont give up the code

so i have to do database fudges until a new front end is built

Then, dont update the idnetity column
Just generate the serial no in the select statement as shown in the example

Madhivanan

Failing to plan is Planning to fail

erics44
Starting Member

12 Posts

Posted - 2009-12-08 : 09:28:04

The thing is the sql coming from the front end is

select Fields from theTable Order by id

and i cant change that, so its always going to order by id

I have actually reinserted the information into the table ordered the same way and deleted the old records

I know it is probly a rubbish way

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-08 : 09:34:58

quote:
I have actually reinserted the information into the table ordered the same way and deleted the old records

I know it is probly a rubbish way

In the circumstances, that was probably your best option

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources