Solved

SQL update record rows based off of rank

Posted on 2011-10-01
13
279 Views
Last Modified: 2012-05-12
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
Comment
Question by:brihol44
  • 6
  • 5
  • 2
13 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
What is the database system and version?
0
 

Author Comment

by:brihol44
Comment Utility
Oh sorry... MySQL 5
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 
LVL 31

Expert Comment

by:awking00
Comment Utility
select rank() over (order by Site_Points desc)
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
awking00, this is MySQL 5; rank() is not available.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 31

Expert Comment

by:awking00
Comment Utility
Sorry, I was thinking SQL Server 2005
0
 

Author Comment

by:brihol44
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 

Author Comment

by:brihol44
Comment Utility
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
 

Author Comment

by:brihol44
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 

Author Closing Comment

by:brihol44
Comment Utility
Finally had time to work on this. Thanks.! Worked well.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
What does this SQL mean? 7 41
SQL Update Query 23 78
return result by latest date - oracle query 21 47
PL/SQL LOOP CURSOR 3 39
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 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

11 Experts available now in Live!

Get 1:1 Help Now