Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-11-14
5
Medium Priority
?
200 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

721 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