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

Not really. I think Oracle gives you a "ROWID" or something like that, but most don't give you one. A custom ordering, like a column in your database that tells you want position the entry in the gallery is good because you can never be sure that SQL will put things in the table in the order you think they should be in.

Answer 2

As you are not specific about what database you're using, in SQL Server 2005 you could use

SELECT 
  ROW_NUMBER() OVER (ORDER BY PhotoID)
  , PhotoID
FROM dbo.Photos

Answer 3

You don't say what DBMS you are using, and the "solution" will vary accordingly. In Oracle you could do this (but I would urge you not to!):

select photo, offset
from
( select photo
  ,      row_number() over (partition by gallery_id, order by photo_seq) as offset
  from   photos
)
where  id = 123

That query will select all photos (full table scan) and then pick out the one you asked for - not a performant query!

I would suggest if you really need this information it should be stored.

Answer 4

Assuming the position is determined solely by the id, would it not be as simple as counting all records with a smaller id value?:

select
    po.[id]
    ...
    ((select count(pi.[id]) from photos pi where pi.[id] < po.[id]) + 1) as index
    ...
from photos po
...

I'm not sure what the performance implications of such a query would be, but I would think returning a lot of records could be a problem.

Answer 5

You must understand the difference between a "application key" and a "technical key".

The technical key exists for the sole purpose to make an item unique. It's usually in INTEGER or BIGINT, generated (identity, whatever). This key is used to locate objects in the database, quickly figure out of an object has already been persisted (IDs must be > 0, so an object with the default ID == 0 is not in the DB, yet), etc.

The application key is something which you need to make sense of an object within the context of your application. In this case, it's the ordering of the photos in the gallery. This has no meaning whatsoever for the database.

Think ordered list: This is the default in most languages. You have a set of items, accessed by an index. For a database, this index is an application key since sets in the database are unordered (or rather the database doesn't guarantee any ordering unless you specify ORDER BY). For the very same reason, paging through results from a query is such a pain: Databases really don't like the idea of "position".

So what you must do is add an index row (i.e. an INTEGER which says at which position in the gallery your image is; not a database index for quicker access, even though you should create an index on this column ...) and maintain that. For every insertion, you must UPDATE index = index + 1 where index >= insertion_point, etc.

Yes, it sucks. The only solution I know of: Use an ORM framework which solves this for you.

Answer 6

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;

Answer 7

There's no need for an extra table... why not just count the records?

You know the order in which they are displayed [which can vary], but you know it.

You also know the ID of the current record... so, let's say it's ordered on date:

The offset of the record, is the total number of records counted with a date < that date.

SELECT COUNT(1) FROM ... WHERE date < "the-date"

.. this gives you the number you can use as the offset for the other queries...