Solved

How do I return a row number or place of a record in a sql table?

Posted on 2007-11-14
5
196 Views
Last Modified: 2010-03-19
I run an ASP.NET website with a monthly contest.  Members can enter 1 script for each contest.  Scripts can then be voted on.

There are three relevant SQL tables:

Contest with the field: ContestID
Script with the fields: ContestID, ScriptID, and Title
ScriptComment with the fields: ScriptID and Score

Scripts are grouped by contest and come in place (first, second, third, etc.) based on the average of all the scores they received.

I need a QUICK stored procedure that will accept ScriptID as a parameter and return back the place that script came in, in that particular contest.

Currently, I get this information by creating a virtual table and populating it with joined data from Script and ScriptComment.  I order the table by average Score desc.  I then use a select statement against that table using the ScriptID and return back the row number.  This works, but it is very slow.  There are times where I might need to list 300 scripts and I need to do this lookup for each script to display what place they came in.

I need a much faster way to get the place of any script in a contest.  I think the Virtual Table is killing the speed.  I have been trying to find a solution using a view or the new SQL 2005 command Row_Number(), but I have been unsuccessful.

If anyone has a speedy solution, I would love to hear it.

Thanks a lot,

Chris Messineo
0
Comment
Question by:CMessineo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:dbbishop
ID: 20283966
I assume this needs to be done in very real time? If there can be a slight lag (say 30-minutes, create a staging table that has an IDENTITY column. Create a clustered index on ScriptID. Use your same method, but sort in average score order and use

INSERT INTO tblStaging
SELECT ...
ORDER BY avgScore

Schedule a job to run every 30-minutes to

TRUNCATE TABLE tblStaging
INSERT (above code)

Then run your query against the staging table. I would put a TABLOCKX hint and wrap the truncing and inserting of records in a transaction. That way, if someone tries to access a socre while the table is reloading, it will block them.
0
 
LVL 15

Accepted Solution

by:
JimFive earned 500 total points
ID: 20284231
Try something like:

Select Count(*) as rank, a.ScriptID,  a.Score
From (Select ScriptID, Avg(Score) as Score FROM ScriptComment group by ScriptID) a
      JOIN (Select ScriptID, Avg(Score) as Score FROM ScriptComment group by ScriptID) b
            ON a.Score >= B.Score
group by a.Nm, a.score
order by 1
0
 

Author Comment

by:CMessineo
ID: 20284268
Thanks for quick response.

Unfortunately, everything in the on the site runs in real time - especially the annoucement of the monthly winners.  So a delayed solution (even only 30 minutes) would cause havoc, since there is a good chance the results could change in that time.
0
 

Author Comment

by:CMessineo
ID: 20284346
JimFIve:

I am trying to implement your suggestion.  Two questions though:

First, in the group by what is "a.Nm"?

Second, where do I specify the ScriptID I want to get the place of?  For example, if I pass in 333 as the ScriptID, that should return back a a number representing the place that script came in it's contest.

Thanks,

Chris
0
 
LVL 15

Expert Comment

by:JimFive
ID: 20285242
Sorry, a.Nm should be a.ScriptID

This query actually shows the complete ranking to get a specific rank add WHERE a.ScriptID = @ScriptID  You'll also need to add the ContestID into the inner queries.

--
JimFive
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question