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-exchang
Main Topics
Browse All Topics
by: snoyes_jwPosted on 2005-05-12 at 10:22:40ID: 13988880
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;