this *should* be simple... - 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 2000 Forums

SQL Server Development (2000)

this *should* be simple...

Author

Topic

sengoku
Starting Member

29 Posts

Posted - 2003-09-26 : 05:58:42

*grin* don't you love posts with that kinda title?

anyway, here's my problem.

i have a table that gets a bunch of results inserted into it. say money made per day, for example.

what i would like to do is go through that table and assign a 'position' to each one. so the biggest earner gets '1', the 2nd biggest gets '2' etc etc.

at the moment i've got a stored proc doing it, with a cursor that simply does a
SELECT * FROM table order by moneymade desc

the loops through that cursor with a counter starting at 1 and incrementing evey iteration, and then
update table set myrating=@counter where row_autokey=xxx

so, my question is, is there a neat query that will do this all in 1 go? and if so, why can't my little brain think of it? :)

thanks in advance, for any help...

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-09-26 : 06:24:53

you need to search/read the articles dealing with ranking/sequences....there's a formal article on this....written in the early days of SQLTeam.com.

one topic that may help is http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11230

also http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6027

sengoku
Starting Member

29 Posts

Posted - 2003-09-26 : 06:50:25

oooh, i'm proud of myself :)

this works!

update testing set test_pos=(select count(test_au) from testing t where t.test_var>=testing.test_var)

:)

[edit : thanks for the info andrew, it did help a bit :)]

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-26 : 07:54:38

sengoku,

do you mean no duplicates among moneymade values
or you don't mind possible gaps in myrating values?

PS
Only one thing:

update table set myrating=@counter where row_autokey=xxx

Why not:

update table set myrating=@counter where current of yourCursorName

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-26 : 10:06:39

Is it tyhis easy?



USE Northwind
GO

SELECT * INTO myTable99 FROM [Order Details] WHERE 1=0
GO

ALTER TABLE myTable99 ADD myOrder int IDENTITY(1,1)
GO


INSERT INTO myTable99 (OrderID, ProductID, UnitPrice, Quantity, Discount)
SELECT * FROM [Order Details] Order by Quantity DESC
GO

SELECT * FROM myTable99 Order by myOrder
GO

DROP TABLE myTable99
GO

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-26 : 14:36:28

This is even easier

SELECT IDENTITY(int,1,1), *
INTO SequencedTable
FROM Orders
ORDER BY OrderValue DESC

Owais

Make it idiot proof and someone will make a better idiot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources