Geek Answers Handbook

How to find the * position * of one record in a limited, randomly ordered set of records?

MySQL

Suppose you want to get only one record using some id, but you want to know what its position would be if you ran into it in a large ordered set.

The fact is that this is a photo gallery. You land on one photo, but the system must know that its offset is in the entire gallery.

I suppose I could use custom indexing fields to track positions, but there should be a more elegant way in the SQL version.

+5
source share
7 answers

, #, ORDER BY, . . , / ...

(photo_gallery_id, date_created_on), ( ), , ( , gallery_id 90% -).

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;
+2

. , Oracle "ROWID" - , . , , , , , SQL , , .

0

, , SQL Server 2005

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

, , "" . Oracle ( !):

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

( ), , , !

, , .

0

, , , id?:

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

, , , .

0

" " " ".

- . INTEGER BIGINT, (, ). , , ( > 0, == 0 ) ..

- , , . . .

: . , . , (, , , ORDER BY). : "".

, , ( INTEGER, , , , ... ) . UPDATE index = index + 1 where index >= insertion_point ..

, . , : ORM, .

0

, ?

( ), .

; , :

- , < .

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

, ...

0

More articles:


All Articles