get ranking from scores table

nunomira

Hi,

Supposing I have this table (MySQL : 4.0.23-standard)

[code]

-- Table structure for table `pontos`

CREATE TABLE pontos (
pontos_id mediumint(6) unsigned NOT NULL auto_increment,
pontos smallint(5) unsigned NOT NULL default '0',
user_id mediumint(6) unsigned NOT NULL default '0',
data timestamp NOT NULL,
PRIMARY KEY (pontos_id),
KEY user_id (user_id)
) TYPE=InnoDB AUTO_INCREMENT=17 ;

-- Dumping data for table `pontos`

INSERT INTO pontos VALUES (1, 2435, 4, '2005-08-31 19:43:21');
INSERT INTO pontos VALUES (2, 2123, 4, '2005-08-31 19:43:36');
INSERT INTO pontos VALUES (3, 2345, 2, '2005-09-07 16:35:46');
INSERT INTO pontos VALUES (4, 3421, 2, '2005-09-07 16:35:46');
INSERT INTO pontos VALUES (5, 1234, 2, '2005-09-07 16:36:07');
INSERT INTO pontos VALUES (6, 1235, 4, '2005-09-07 16:36:07');
INSERT INTO pontos VALUES (7, 253, 1, '2005-09-07 16:36:24');
INSERT INTO pontos VALUES (8, 543, 1, '2005-09-07 16:36:24');
INSERT INTO pontos VALUES (9, 897, 1, '2005-09-07 16:36:37');
INSERT INTO pontos VALUES (10, 765, 1, '2005-09-07 16:36:37');
INSERT INTO pontos VALUES (11, 457, 2, '2005-09-07 16:36:48');
INSERT INTO pontos VALUES (12, 352, 4, '2005-09-07 16:36:48');
INSERT INTO pontos VALUES (13, 1111, 3, '2005-09-07 16:37:09');
INSERT INTO pontos VALUES (14, 2345, 3, '2005-09-07 16:37:09');
INSERT INTO pontos VALUES (15, 2334, 3, '2005-09-07 16:37:33');
INSERT INTO pontos VALUES (16, 2134, 4, '2005-09-07 16:37:33');
[/code]

How can I get the ranking of a user_id, depending on the user's best result, i.e. higher score (pontos)?

I guess I have to have the DISTINCT list of users, and each user's best score.

thorpe

this should retrieve the highest pontos for user_id 1.

SELECT DISTINCT MAX(pontos) FROM pontos WHERE user_id = 1;

nunomira

hi,

Thank you for your answer.
Unfortunately that's not what I'm after.

That gives you the highest score in the table, and would be the same as:

SELECT MAX(pontos) FROM pontos

Maybe I wasn't clear enough, and I apologize for this:
given a certain user_id, I want his ranking, which I think would correspond to get a list of distinct users, along with their best scores.
With this, I'd get the user's ranking.

I guess it's this table with the highlighted data only:

[edit]
This was regarding your first answer:

SELECT DISTINCT MAX(pontos) FROM pontos

I appreciate the second answer, but still not there.
[/edit]

nunomira

I ended up with this for the table with the unique user_ids and the maximum pontos (scores):

SELECT DISTINCT user_id, MAX(pontos) as pontos FROM `pontos`  GROUP BY user_id
ORDER BY pontos DESC

The solution I found was creating a temporary table with that query, and then query this temporary table:

CREATE TEMPORARY TABLE temp AS SELECT DISTINCT user_id, MAX(pontos) as pontos FROM `pontos`  GROUP BY user_id
ORDER BY pontos DESC

The PHP I used to test is the following:

mysql_pconnect ('localhost', 'root', '');
mysql_select_db ('test');

// create the temporary table
$query = "CREATE TEMPORARY TABLE temp AS SELECT DISTINCT user_id, MAX(pontos) as pontos FROM pontos  GROUP BY user_id
ORDER BY pontos DESC";

mysql_query ($query);

// get the score (pontos) of a user
$user_id = 2; // for testing 
$query = "SELECT pontos FROM temp WHERE user_id = '$user_id'";
$result = mysql_query  ($query);
$row = mysql_fetch_assoc ($result);

$pontos =  $row['pontos'];
echo 'pontos -> ' . $pontos;

// see how many of the other users have greater scores, this is the ranking
$query = "SELECT COUNT(*) AS ranking FROM temp WHERE pontos > '$pontos'";
$result = mysql_query  ($query);
$row = mysql_fetch_assoc ($result);

$ranking =  $row['ranking'];
$ranking ++;
echo 'ranking -> ' . $ranking;

// drop the temporary table and close the connection
$query = "DROP TEMPORARY TABLE IF EXISTS temp"; 
$result = mysql_query  ($query);
mysql_close ();

This takes some steps.
If there is an easier way to do this, I'll be willing to hear and learn it!