• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 807
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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