Need help getting number sequences into a field 1

[tmar]

I've run into this a few times and have an bad work-around but wanted to see if there's a better way.

Often when I run queries & views I need to know the sequences of the returned values.

For instance if I run a query like "select Custid from tarcustomer order by totalSales desc" I'd like a return with the number sequence like:

1 Microsoft
2 Intel
3 Coke
.
.
515 Fred's Diner

What I've been doing is selecting the value into temptable and using an identifier field to count the returns and then re-running a select from that table (duck tape approach).

 

[PWise]

in 2005 you can use rank()

 

[tmar]

For the next year I'm in 2000. Is there a solution there?

 

[gmmastros]

If you're stuck with sql2000, then keep using your duck tape.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SkipVought]

You can get duck tape in a variety of colors now. ;-)

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[TysonLPrice]

There's probably better ways but you could add a temporary column to use as a counter and then drop it:

--Create a temporary table sorting the way you want
Select * into #TestIt From YourTable
order by business

-- Add a counter column after the temporary table is created
Alter TABLE #TestIt ADD Ctr int 

-- update the counter
declare @updateValue int
SET @updateValue = 0 
update #TestIt
set Ctr  = @UpdateValue ,
@updateValue = @updateValue + 1 

select Ctr, Business from #TestIt

 

[PWise]

TysonLPrice:

I already impleamented your method so have a Star

but instaed of a update i added a IDENTITY coulmn

Select * into #TestIt From YourTable
order by business

Alter table #TestIt add ctr int IDENTITY (1, 1) NOT NULL 

Select Ctr, Business from #TestIt