Stored proc ordering 2

[MarkGreen]

Hi,
Not sure how easy this is but i'd thought i'd ask anyway...

I need to put ordered integers into a field(order) depending on the value of another field(date)

So the order field ends up with the higher numbers in the records where the date field is further into the future.

So for example,
if the 3 records are

date order
26/06/04
15/01/04
04/07/04

then the stored procedure would put the following values into the order field:

date order
26/06/04 2
15/01/04 1
04/07/04 3

In fact, as long as the order numbers are in the correct sequential order it doesnt matter if there are any gaps.

Any help is really appreciated..
Mark.

 

[swampBoogie]

update t
set orderColumn = (select count(*) + 1 from yourTable
where date > t.date)
from yourTable t

 

[MarkGreen]

thanks for your help,
i understand what your trying to do, but not well enough to work out whats wrong with it!

Here is the eactly code that i've put in the stored proc:

update milestone_tester
set milestone_order = (select count(*) + 1 from milestone_tester
where milestone_date > milestone_tester.milestone_date)

but at the moment its only putting a 1 into the milestone_order field for every record.

I assume this is because the select count is returning 0. Any ideas whats wrong?

 

[JamesLean]

You've missed the crucial aliasing:

update m1
set milestone_order = (select count(*) + 1 from milestone_tester where milestone_date > m1.milestone_date)
from milestone_tester m1

--James

 

[MarkGreen]

thats excellent. I appreciate it.