Solved

SQL update record rows based off of rank

Posted on 2011-10-01
13
309 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
[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
  • 6
  • 5
  • 2
13 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36898283
What is the database system and version?
0
 

Author Comment

by:brihol44
ID: 36898287
Oh sorry... MySQL 5
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36898318
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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36898319
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 32

Expert Comment

by:awking00
ID: 36905163
select rank() over (order by Site_Points desc)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36905208
awking00, this is MySQL 5; rank() is not available.
0
 
LVL 32

Expert Comment

by:awking00
ID: 36905229
Sorry, I was thinking SQL Server 2005
0
 

Author Comment

by:brihol44
ID: 37014565
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 60

Expert Comment

by:Kevin Cross
ID: 37014792
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
ID: 37051647
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
ID: 37051653
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 60

Expert Comment

by:Kevin Cross
ID: 37054835
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
ID: 37089339
Finally had time to work on this. Thanks.! Worked well.
0

Featured Post

Industry Leaders: 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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 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