A query, which contains rows position in another query (ranking)

Question

I am building my ranking using query like this:

SELECT * FROM users ORDER BY fame DESC;

I count position by incrementing a variable in rows loop

I want to add a ranking position to my profile sub-page, without fetching all the ranking first.

I thought of joining two queries, but I have no idea how to acheive this...

Not sure what this means "without fetching all the ranking first." But you can use Rank() function instead of incrementing a variable — cjava, Oct 03 '13 at 10:45
I think you should be using `SQL COUNT()` function and possibly look into `SQL RANK`. you should really get SQL to return the data you want to display first. http://technet.microsoft.com/en-us/library/ms176102.aspx http://technet.microsoft.com/en-us/library/ms175997.aspx — Squirrel5853, Oct 03 '13 at 10:47
@SecretSquirrel: MySQL does not support window functions (`rank()`) — , Oct 03 '13 at 10:51
take a look here http://stackoverflow.com/questions/3333665/mysql-rank-function — Squirrel5853, Oct 03 '13 at 10:54

score 0 · Answer 1 · answered Oct 03 '13 at 11:03

0

Try something like this.

SELECT 
@rownr := @rownr + 1 AS row_number,
users.*
FROM users
JOIN    (SELECT @rownr := 0) r
ORDER BY fame DESC;

answered Oct 03 '13 at 11:03

Mad Dog Tannen

YEs you can, provided that the users table has a filed called row_number for instance. – Mad Dog Tannen Oct 03 '13 at 13:15

1 Answers1