• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 805
  • Last Modified:

MySQL Sequence number of a ORDERed record

Hello Experts,

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.
0
German_Rumm
Asked:
German_Rumm
  • 2
  • 2
1 Solution
 
snoyes_jwCommented:
Which version of MySQL do you have?

If all you want is to find what position it is in, and you have version 4.1 or later, you could do something like
SELECT COUNT(*) FROM photo WHERE views > (SELECT views FROM photo WHERE id = 227);
Of course, anything with the same number of views will tie.  You could use a timestamp field to break ties:
SELECT COUNT(*) FROM photo WHERE views > (SELECT views FROM photo WHERE id = 227) OR (view = (SELECT views FROM photo WHERE id = 227) AND theTimeStampField < (SELECT theTimeStampField FROM photo WHERE id = 227));

If you want the 10 records either side of the the field, you could do something like
(SELECT * FROM photo WHERE views > (SELECT views FROM photo WHERE id = 227) ORDER BY views ASC LIMIT 5)
UNION
(SELECT * FROM photo WHERE views > (SELECT views FROM photo WHERE id = 227) ORDER BY views DESC LIMIT 5)
ORDER BY views DESC;
0
 
German_RummAuthor Commented:
snoyes_jw,

I have MySQL/4.1.

Ok, your first query did work for me, but with 400 rows in table it had to go through 300 for "not so popular" photo.
It's far more efficient than my solution though, so I will use that.

Your second (UNION) query didn't give me what I wanted. First 5 records were those that are more popular than current photo, the last 5 were the most popular.

Here is more detailed explanation:
photoID = 400, ViewCount = 226
Second query gave me:
232
238
242
244
245
3032
2027
1876
1683
1549
which is not really a correct sequence. And what if I want this sequnce to have rank along with the records, just like ranking here?
http://www.experts-exchange.com/memberRank.jsp?mid=1060720 - sorry for using your username :-)
0
 
snoyes_jwCommented:
My UNION query uses the wrong > and <.  Try this:
(SELECT * FROM photo WHERE views > (SELECT views FROM photo WHERE id = 227) ORDER BY views ASC LIMIT 5)
UNION
(SELECT * FROM photo WHERE views < (SELECT views FROM photo WHERE id = 227) ORDER BY views DESC LIMIT 5)
ORDER BY views DESC;

0
 
German_RummAuthor Commented:
snoyes_jw,

Thank you. Used `<=` with `LIMIT 6` to include current photo in result.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now