How to find the *position* of a single record in a limited, arbitrarily ordered record set?

Question

MySQL

Suppose you want to retrieve just a single record by some id, but you want to know what its position would have been if you'd encountered it in a large ordered set.

Case in point is a photo gallery. You land on a single photo, but the system must know what its offset is in the entire gallery.

I suppose I could use custom indexing fields to keep track of positions, but there must be a more graceful way in SQL alone.

Answer 1

So, first you create a virtual table with the position # ordered by whatever your ORDER BY is, then you select the highest one from that set. That's the position in the greater result set. You can run into problems if you don't order by a unique value/set of values...

If you create an index on (photo_gallery_id, date_created_on) it may do an index scan (depending on the distribution of photos), which ought to be faster than a table scan (provided your gallery_id isn't 90% of the photos or whatnot).

SELECT @row := 0; 
SELECT MAX( position ) 
  FROM ( SELECT @row := @row + 1 AS position
           FROM photos
          WHERE photo_gallery_id = 43
            AND date_created_on <= 'the-date-time-your-photo-was' 
          ORDER BY date_created_on ) positions;