Solved

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

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

706 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now