1

Let's say I have this table:

ID Score
1   345
2   5
3   76
4   45
5   85
6   175
7   209

and this is my query:

SELECT * FROM posts ORDER BY Score DESC

How do I get the rank/position of each row?

Jürgen Paul
  • 14,299
  • 26
  • 93
  • 133
  • ^But I will be iterating on the result (there's no where statement) and not just focusing on a single row. – Jürgen Paul Feb 19 '12 at 04:51
  • It means i'm not selecting a single row, i'm selecting all the rows. – Jürgen Paul Feb 19 '12 at 04:54
  • 2
    If you'll be iterating on the result in some language, surely you can keep track of the rank there? You don't need to have the database give it to you if it's ephemeral and applies only at the time you query. – Jim Garrison Feb 19 '12 at 04:56
  • its not possible without a where statement in direct query.. Witout mentioning id or score in where clause you can not get the position of that particular row.. – Manigandan Arjunan Feb 19 '12 at 04:59
  • @JimGarrison, you got me there! Thank you. – Jürgen Paul Feb 19 '12 at 05:01
  • see my answer... you will get rank as what you want... Also see [here](http://stackoverflow.com/questions/8878354/ranking-joint-positions-in-mysql) – Fahim Parkar Feb 19 '12 at 06:03

2 Answers2

1

You can use a technique like @OMG Ponies linked to above like this:

SELECT posts.*, @row:=@row+1 as 'rank' FROM posts, (SELECT @row:=0) r ORDER BY posts.Score DESC
Brian Glaz
  • 15,468
  • 4
  • 37
  • 55
0

If you run below code, you will see new column as Position which is same as Rank

SELECT ID, Score, 
(SELECT COUNT(distinct u2.Score) FROM myTable u2 
WHERE 
u2.Score > u1.Score) + 1 AS position FROM myTable u1
ORDER BY position

Let me know if you have any questions...

Fahim Parkar
  • 30,974
  • 45
  • 160
  • 276