Get certain rank out of table.

[crimsonmoon]

I have a table with data in it.

 

I sort that data by doing an order by FIELD ASC

 

Now after doing my Order By how would I tell what rank or position one field was in the table.

 

Basically I'm wanted to rank a bunch a field and let the user know what rank they are at.

[Wildbug]

You can use an incrementing session variable as a column in your select.

 

SELECT @rank:=0;
SELECT user,rank_field,@rank:=@rank+1 AS rank FROM yourtable ORDER BY rank_field;

 

Or you could do this in PHP with an incrementing variable.

[crimsonmoon]

So the way it sounds there's not a way to do it limited without pulling the whole table.

 

For intance I wanted to show the top five by doing a order by field asc limit 5

 

But to do this I have to search it until I find the right row.

 

There'll be several of these charts on a page so I was trying to limit the load.

[Wildbug]

So add a LIMIT clause.

[crimsonmoon]

If I add the limit clause it won't pull the entire table of around 6000 rows.

 

I want to take the table and sort it by a field and be able to pull a rank out for the current user no matter where they are.

 

Is there any way to do this without make a PHP variable that increments until it picks you out.

[crimsonmoon]

The only way I can think of would be to do a select on the table and order by asc and then increment a PHP variable until $yourid = $select[yourid] or something and then print the value of the rank.

 

I know that will work just fine.

 

Just wondering if they was a select that would do it without need to loop through the entire table to find out.

[Wildbug]

If I add the limit clause it won't pull the entire table of around 6000 rows.

 

Uh, isn't that what you wanted?

 

The ORDER BY is performed before the results are returned.  The LIMIT only returns so many of those rows.  LIMIT does not apply to the rows that ORDER BY gets; it's applied after ORDER BY.

 

If you want the rank of a specific user, you can use a HAVING clause:

SELECT @rank:=0;
SELECT user,rank_field,@rank:=@rank+1 AS rank FROM yourtable ORDER BY rank_field HAVING user=15;

 

To get top 5:

SELECT @rank:=0;
SELECT user,rank_field,@rank:=@rank+1 AS rank FROM yourtable ORDER BY rank_field LIMIT 5;

 

[crimsonmoon]

Tried that and it didn't work.

 

here is what  I have

 

mysql_query("SELECT @rank:=0;");

$myrank = mysql_fetch_array(mysql_query("SELECT id,user,rankfield,@rank:=@rank+1 AS rank FROM table ORDER BY rankfield desc HAVING id=$user[id];"));

 

Your Rank Is: <?php print "$myrank[rank]"; ?>

[Wildbug]

That's odd.  I guess HAVING BY comes before ORDER BY and will affect the results adversely.  I thought it was always at the end (just before LIMIT).  GROUP BY won't work either.

 

Well, you can use a subquery.  This has been tested and works correctly in the case of "tied" values.

mysql> SELECT * FROM t2;
+------+----------+-------+
| id   | rank_col | user  |
+------+----------+-------+
|    1 |      100 | Bob   |
|    2 |      350 | Sue   |
|    3 |      125 | Alley |
|    4 |     3000 | Tim   |
|    5 |       45 | Joe   |
|    6 |     1550 | Steph |
|    7 |      350 | Doug  |
+------+----------+-------+
7 rows in set (0.00 sec)

mysql> SELECT @rank:=0; SELECT * FROM (SELECT rank_col,@rank:=@rank+1 AS Rank FROM t2 GROUP BY rank_col) AS scoreRanks JOIN t2 ON scoreRanks.rank_col=t2.rank_col WHERE user="Sue";
+----------+
| @rank:=0 |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

+----------+------+------+----------+------+
| rank_col | Rank | id   | rank_col | user |
+----------+------+------+----------+------+
|      350 |    4 |    2 |      350 | Sue  |
+----------+------+------+----------+------+
1 row in set (0.00 sec)

mysql> SELECT @rank:=0; SELECT user,Rank FROM (SELECT rank_col,@rank:=@rank+1 AS Rank FROM t2 GROUP BY rank_col) AS scoreRanks JOIN t2 ON scoreRanks.rank_col=t2.rank_col WHERE user="Sue";
+----------+
| @rank:=0 |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

+------+------+
| user | Rank |
+------+------+
| Sue  |    4 |
+------+------+
1 row in set (0.02 sec)

 

(Don't include the semi-colons when using these queries in PHP.)

[crimsonmoon]

I might have to scratch that idea and just show it if they are in the top 5.

 

The Issue I'm running into is I'm going to have around 20 of these rankings on one page and when you x that query by 20 I"m getting page load times around 1 to 3 seconds and I'm afraid if too many go to look it my really spike up the server load.

 

I'm going to have an option to view all of that type on one page broken up by different pages so I'll just show it on there that why there's only one query.

[Wildbug]

Maybe you can combine some of the queries to reduce the strain on the server?

[bubblegum.anarchy]

Yeah, I never tested the SELECT @id := in php - does not appear to work.

 

Use SQL_CALC_FOUND_ROWS in the LIMITed query then SELECT found_rows() and some math to calculate the rank in php.

 

 

[Wildbug]

It works, but you have to run it like:

 

mysql_query('SELECT @rank:=0');

[bubblegum.anarchy]

Great Wildbug... the following works also:

 

mysql_query("SET @id = 0");
mysql_query("SELECT @id := @id + 1 FROM table");

 

But I am unsure why this does not:

 

mysql_query("SELECT @id := ifnull(@id, 0) + 1 FROM table");

[crimsonmoon]

Would this still have some load being done several times?

 

I went ahead and set it up different. I let them know if they are in the top 5 and then on the page that displays the ranking in detail I give the exact rank since that page only has one table call on it.

 

The server likes it much better.

 

Still if this would not have much load done 20 times on the page it would be nice.

[Wildbug]

Why do you have twenty rankings?  Are they completely seperate SQL queries?  Are they the same type of ranking?  If so, you may be able to combine some of them into a common query.  Can I see your ranking queries?

 

ba: "SET @a = 0" is probably the preferred method.  I always forget to do it that way, though.

[fenway]

Great Wildbug... the following works also:

 

mysql_query("SET @id = 0");
mysql_query("SELECT @id := @id + 1 FROM table");

 

But I am unsure why this does not:

 

mysql_query("SELECT @id := ifnull(@id, 0) + 1 FROM table");

Variables have strange evaluation time.