Why not use the WHERE clause? I.e. in your last example, simply do

SELECT FIRST 20 COL, ID
FROM TEST WHERE COL >= 'kl'
ORDER BY COL

Of course it is possible to find the position by doing something like:

SELECT COUNT(*) FROM TEST WHERE COL < 'kl'

This requires COL to be unique or you to add some logic to handle
duplicates (it isn't difficult). Though I'd say the first solution is
better (you may have reasons for doing it your way that I've not thought
about).

Set

jasperelzinga wrote:

> 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