Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

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

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
CMessineo
Asked:
CMessineo
  • 2
  • 2
1 Solution
 
dbbishopCommented:
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
 
JimFiveCommented:
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
 
CMessineoAuthor Commented:
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
 
CMessineoAuthor Commented:
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
 
JimFiveCommented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now