Hello,

Question in short: Is it possible to get the position of a record in a
certain sorted recordset?

Lets begin with an example:

Table:
CREATE TABLE test (id INTEGER, col1 VARCHAR(100), col2 VARCHAR(100))

With the following data:

id | col1 | col2
1 | a | a
2 | b | c
3 | c | b

If you take this query as example:
SELECT id FROM test ORDER BY col1

This wil return [1,2,3]. So the position of id '2' will be 1.

Now.. if you take this query:
SELECT id FROM test ORDER BY col2

This will return [1,3,2]. The position of '2' will be 2 in this situation.

How do i calculate this with just SQL?

And another example:

Table:
CREATE TABLE test (id INTEGER, col VARCHAR(100))

pos col id

1 aa 15
2 aw 2
3 bb 57
4 bd 12
5 bf 34
6 cd 872
7 .. ..
8
9
.
.
.
? kl 645

How do I get the index number/position of kl (with id 645)?

Some background information:

What we normally do is get the whole list of id's and store it in
memory. And if we want the position of a certain id, we check it with
the list of id's and see what the position is. But.. when
recordnumbers exceed 10.000, this will get very slow, because for
every action (adding, deleting, sorting) the list of 10k id's must be
recalculated.

Our new plan is to only extract the desired part of the database that
has to be shown on the screen using the FIRST x SKIP y function. This
will speed up our application enormously but the list of id's we were
used to have in memory is gone.

So.. we're forced to do this operation in SQL now. Is there any way to
do this?

Thanks in advance,

Jasper Elzinga