• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

SQL update record rows based off of rank

Hello,

I'm looking to update 100's of rows of data that have a site point value. Each record is given a site points for particular parameters. What I'm trying to do is give each record a rank from 1 to whatever so that the highest site point value will be given a rank of 1 and so on based off the order value of the site points.

So for example if I have the first three rows of data of...

Row 1 Site Points :  10
Row 2 Site Points :  15
Row 3 Site Points :  5

Row 1 Site Points: 10 Rank: 2
Row 2 Site Points: 15 Rank: 1
Row 3 Site Points: 5 Rank: 3

Thanks,

Brian
0
brihol44
Asked:
brihol44
  • 6
  • 5
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
What is the database system and version?
0
 
brihol44Author Commented:
Oh sorry... MySQL 5
0
 
Kevin CrossChief Technology OfficerCommented:
Depending on the table structure, you should be able to use the @rownum technique I show here:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html

A number of alternatives are shown in the article, including a subquery that does count of the rows that are > the one you are on currently.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Kevin CrossChief Technology OfficerCommented:
Give it a try and post back your query if you are having difficulty and will help you through getting it correctly for your specific case.
0
 
awking00Commented:
select rank() over (order by Site_Points desc)
0
 
Kevin CrossChief Technology OfficerCommented:
awking00, this is MySQL 5; rank() is not available.
0
 
awking00Commented:
Sorry, I was thinking SQL Server 2005
0
 
brihol44Author Commented:
I'm back on this part of the project. In this example I'm not seeing the actual UPDATE part. I'm probably confused here :) ... Can I UPDATE the rank based off the SITE POINTS and ORDER of DATE value (just in case any SITE POINTS are tied?


So far my data is just....

Date                 Site Points             Rank  (Not yet populated/updated but will be once a week or as the administrator pushes a button to do so.)    

10/10/10                    10                   0
09/09/09                    14                   0
08/08/08                    12                   0

Thx,

Brian


0
 
Kevin CrossChief Technology OfficerCommented:
So you are storing the RANK? Well first you need to generate it. Did you read through the link and try to see if you understand how to create the row numbers using your table structure?
0
 
brihol44Author Commented:
Hello,

Yes, I read through all of the SQL statements. Did I miss how I actually update my table? All of the SQL statements I read seem to be just pulling the data.

Thx,

Brian
0
 
brihol44Author Commented:
Ok, I might be thinking about this incorrectly. Maybe I shouldn't store the RANK? Maybe It should just be pulled every time each time a record is shown? I'll see what I can do on my own and report back.

Thx,

Brian
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, I would not recommend storing the RANK. The Article I linked to shows a number of the ways to generate a ranking in MySQL.
0
 
brihol44Author Commented:
Finally had time to work on this. Thanks.! Worked well.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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