Solved

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

Posted on 2007-11-14
5
193 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

749 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