MySQL Sequence number of a ORDERed record
Posted on 2005-05-12
I have a following question:
I have a `photo` table (id int(11) unsigned auto_increment, photo_name varchar(20), views int(9) unsigned).
Every time someone opens up a photo in their browser I increment `views` by one.
I have a "popular photos" section, where I "SELECT * FROM photo ORDER BY views DESC".
Now I want to know, how do I find record position in that results set?
Say I want to know, what position in rating has photo with ID = 227?
At the moment the only solution I came up with is creating a temporary table with an AUTO_INCREMENT,
then INSERT INTO ... SELECT ... ORDER BY `views` DESC, and then selecting that auto_increment field by photo ID.
I think there must be some other solution? (I use PHP w/ MySQL)
P.S. I want something similar to EE's ranking - To be able not only get that photo, but the surrounding records too.