| Subject | Re: Retrieving position of record in a certain recordset |
|---|---|
| Author | Adam |
| Post date | 2006-07-04T00:17:05Z |
--- In firebird-support@yahoogroups.com, "jasperelzinga" <jasper@...>
wrote:
simply create an output parameter to a stored procedure, set it to 1,
use a for select loop to run through your query and call suspend
inside the loop, then increment the output parameter.
Another way if you are only running this query from a single user is
to create a generator, and return the value it returns for each
record, but that will not work when multiple simultaneous transactions
are involved.
Generating lists of 10000 records in a grid is hardly ever required
and is even less likely to be useful. Make use drill down logic and
these sorts of problems 'go away'
Adam
wrote:
>situation.
> 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
>Don't know about with 'just SQL', with 'just PSQL' it is pretty easy,
> How do i calculate this with just SQL?
simply create an output parameter to a stored procedure, set it to 1,
use a for select loop to run through your query and call suspend
inside the loop, then increment the output parameter.
Another way if you are only running this query from a single user is
to create a generator, and return the value it returns for each
record, but that will not work when multiple simultaneous transactions
are involved.
>In the relational model, records don't have a 'record number'.
> 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.
Generating lists of 10000 records in a grid is hardly ever required
and is even less likely to be useful. Make use drill down logic and
these sorts of problems 'go away'
Adam