Create a view with Rank

Question

Post reply

Create a view with Rank

ew

Old Hand

Points: 391
More actions
July 26, 2010 at 1:41 pm

#100284

edit **This is in SQL SERVER 2000. So ROW_NUMBER () OVER doesnt work
I am trying to create a view with 3 columns ( RANK, Bridge_ID, Score)
The rank column will be calculated based on the score column.
Sample data is from table BRIDGE_SCORE is
Bridge_id | Score
101-101 | 100
555-555 | 50
162-109 | 95
333-999 | 89
443-444 | 68
I want the results to look like this
Rank | Bridge_id | Score
1 | 101-101 | 100
2 | 162-109 | 95
3 | 333-999 | 89
4 | 443-444 | 68
5 | 555-555 | 50
Help!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jvanderberg SSCertifiable Points: 6762 More actions · Answer 1

SELECT

ROW_NUMBER() OVER (ORDER BY Score DESC) AS Rank, Bridge_ID, Score

FROM BRIDGE_SCORE

ORDER BY Score DESC

ew Old Hand Points: 391 More actions · Answer 2

post edited this is in SQL Server 2000 so this does not work

SELECT

ROW_NUMBER() OVER (ORDER BY Score DESC) AS Rank, Bridge_ID, Score

FROM BRIDGE_SCORE

ORDER BY Score DESC

jvanderberg SSCertifiable Points: 6762 More actions · Answer 3

I'm sorry, I failed to notice that.

You can emulate ROW_NUMBER() with a subquery:

SELECT

(SELECT COUNT(*) FROM BRIDGE_SCORE BS2 WHERE BS2.Score > BS.Score)+1 AS Rank,

Bridge_ID, Score

FROM BRIDGE_SCORE BS

ORDER BY Score DESC

The only difference is that ROW_NUMBER() would give two different rank numbers to players with the same score. This will give the same number for ties.

--J

ew Old Hand Points: 391 More actions · Answer 4

Thanks for the reply. The query works but it is taking about 30 minutes for 8987 rows. Another issue is I'm trying to create a view out of this data and the ORDER BY clause is not permitted when creating a view.

I have a stored proc where I can get the results I want but the end user wants to be able to query the results from the SP. Is there any way to query results from a SP?

Here is the code for my SP.

CREATE TABLE #BridgeRanking(rank INT IDENTITY(1,1),

bridge_id VARCHAR(10), score numeric(19,1))

INSERT INTO #BridgeRanking

SELECT bridge_id, score FROM score_calcs

order by score desc

select * from #BridgeRanking

jvanderberg SSCertifiable Points: 6762 More actions · Answer 5

The most performant way to get the data your trying to get is by inserting into a table or temp table with an IDENTITY column. Since you needed a view, I didn't even mention this. It would however be several orders of magnitude faster than the query I suggested.

I didn't realize my suggestion would be as slow as it is, but looking it over it makes sense. For each or your 8,000+ rows it has to perform a COUNT() aggregate, and the number of rows it is counting gets larger every time. I don't think you're going to get it much faster than it is.

I don't know of any other way to get around the problem in SQL Server 2000. I'm failing to find anything in google, either. Perhaps someone else will have another idea, but you may want to look at it from another angle.

Where do your requirements come from? What is binding you to SQL Server 2000? Any chance you could use a more recent express edition? What about the requirement of having a view? Any way you can think to get around that? Just out of curiosity: is this a web app? Is it work-related or a personal project?

If there's no way you can relax your requirements, the only option I can think of is to cache the data, which would add a little bit of latency. If you use the IDENTITY table suggestion, the query should run fast enough that you could recompile the data fairly often. Depending on the server load, you could presumably recreate the table every few minutes.

--J

ew Old Hand Points: 391 More actions · Answer 6

Thanks for the reply. We have a project in the works to upgrade this database to SQL 2008 but for now we are stuck with SQL 2000 🙁

If there's no way you can relax your requirements, the only option I can think of is to cache the data, which would add a little bit of latency. If you use the IDENTITY table suggestion, the query should run fast enough that you could recompile the data fairly often. Depending on the server load, you could presumably recreate the table every few minutes.

I guess for now, I'm going with the table suggestion and refresh is every so often. I dont think this data changes often so I could probably do it daily and be ok.

Thanks for the help.

Ram:) SSCrazy Points: 2246 More actions · Answer 7

Check it if it is useful to you

select identity(int,1,1) as rank,Bridge_id,Score

from into #tmp

bridge_score

select * from #tmp order by score desc

Ram..

🙂