position of a certain record in the result set?

[Ronald van Raaphorst]

Hi all,

Is there a way to find the position of a certain record (id) in a result
set, based on a (autonumber id)?

Example:
My table has 2 fields: id & name, and the contents =
id name
1 Hi
2 There
3 This
4 Is
5 A
6 Quesion
7 But
8 Is
9 It
10 Possible

Say the currently selected record is id=2 > this is the 2nd record in the
result set.

Say i show only 2 records per page, and the set is sorted by id

Select * from mytable order by id asc limit 0,2
will give:

1 Hi
2 There

Now, as my id = 2 (currently selected record), and the selected record is
the 2nd record in the total result set (no limit), but also the second
record of the first page (offset = 0)

if I change the sort order to name, asc, I still want the selected record
(id=2) to be show, so the page that record is on should be calculated:

Select * from mytable order by id asc limit 0,2 will show only the records 5
& 8, so I need to calculate a new offset.

5 A
7 But
1 Hi
4 Is
8 Is
9 It
10 Possible
6 Quesion
2 There
3 This

and now my id =2 is the 8th record in the result set... (or the first record
in the 5th page) (=10records/2 records per page)

Ronald
Compad