1

I have table Users which contains columns: id, name, points, extra_points. How can I get position rank for specific user, when the rank is sum of points and extra_points?

For all users list rank Im using this query: "SELECT id, (points + extra_points) AS total FROM users ORDER BY total desc"; and the while loop with html ol tag (so there is position number). But I dont know how to show the rank for a sinlge user (in user profile page)? Any help will be great.

moinudin
  • 134,091
  • 45
  • 190
  • 216
gordonek
  • 13
  • 1
  • 3
  • This has already been asked: [Row Rank in a MySQL View](http://stackoverflow.com/questions/1964811/row-rank-in-a-mysql-view) – moinudin Jan 03 '11 at 12:10

4 Answers4

4
SELECT users1.id, (users1.points+users1.extra_points) AS total, COUNT(*)+1 AS rank
FROM users users1
INNER JOIN users users2 ON users1.points+users1.extra_points < users2.points+users2.extra_points
WHERE users1.id = $id
moinudin
  • 134,091
  • 45
  • 190
  • 216
  • thanks, this is working :) just needed to change begining as: SELECT users1.id, (users1.points+users1.extra_points) AS... – gordonek Jan 03 '11 at 12:34
  • Thanks for identifying that small error, which I've now fixed! – moinudin Jan 03 '11 at 12:36
  • Note that in strict SQL, this query will need `GROUP BY user1.id, users1.points, users1.extra_points`. This will also limit the result set to only one row (instead of as many rows as the rank is). – Tomas Creemers Aug 21 '13 at 01:31
0

Well, for a single user, you'd change the select query so it looks like 

"SELECT (points + extra_points) AS total FROM users WHERE id = $id LIMIT 1"

Need to specify which user as a WHERE clause. Also, since you presumably know the id already, don't need to select it, and since it's just one user, don't need the ORDER BY either. And LIMIT 1 will stop it from checking the rest of the rows once it's found it.

Phoenix
  • 4,488
  • 1
  • 21
  • 13
  • Oh wait wait, you need to know the rank, where it fits in with the rest. One minute, let me think. – Phoenix Jan 03 '11 at 12:07
  • You'd have to do some sort of SELECT COUNT(*) where their (points + extra_points) is less than the other users (points+extra_points) – Phoenix Jan 03 '11 at 12:10
0

this isn't tested, but i hope you can understand whats my idea - simply use a subselect to count all users with more points:

SELECT
  id,
  (points + extra_points) AS total,
  (SELECT COUNT(*) FROM users WHERE (points + extra_points)) >= (u.points + u.extra_points) AS rank 
FROM 
  users u 
WHERE 
  id = ?;
oezi
  • 51,017
  • 10
  • 98
  • 115
0

Here's the answer if you have a ClientID and Points column in a table called Client:

<CFQUERY DATASOURCE="YourDatasource" name="YourQueryName">
SELECT ClientID, (Pts) AS total,   
(SELECT COUNT(*) FROM Client 
WHERE (Pts) >= (u.Pts)) AS rank  
FROM Client u  
WHERE ClientID = CLIENTID_OF_YOUR_CHOICE_HERE; 
</CFQUERY>
Alas
  • 11
  • Is it just me, or is this not PHP? (Which is what the question is tagged as.) – Jimmy Sawczuk Jul 09 '11 at 18:28
  • 1
    Dude, the question is tagged as PHP and you wrote your answer wrapped in ColdFusion tags. I know what SQL is; I don't know why you have ColdFusion in your answer. Thanks for the personal attack, that was welcome and highly necessary. – Jimmy Sawczuk Jul 31 '11 at 18:08