At 12:03 AM 3/06/2006, you wrote:

>hi
>i'm having the following problem:
>i'm using a dbgrid in my delphi application which at any given moment
>displays a maximum of 20 entries from my table t (i know some of you
>think that's a bad idea but i have to do it, really)
>
>the entries in the grid are ordered as follows:
>
>order by t.term ascending, t.id ascending (because t.term is NOT unique)
>i wrote 2 procedures for that: get_next_20(term, id),
>get_prior_20(term, id)
>
>they both work very well only that when using get_prior_20, the
>sorting order is of course the converse of what it should be because
>the records are fetched from a key entry on downwards
>
>so what i did in my sql-query was write the following
>
>select * from get_prior_20(:term, :id) order by term ascending, id
>ascending
>
>but instead of just reversing the order what this does is (seemingly)
>mix the sorting order up completely
>
>does anybody of you know why?

Sure. Because you are treating a selectable SP as though it were a
real table. It's not. It's completely virtual, all data invented in
the procedure. Rows come out one-by-one (each time SUSPEND) is executed.

>CREATE PROCEDURE GET_PRIOR_20(
> "TR" VARCHAR(80)
> IDIN BIGINT)
>RETURNS (
> ID BIGINT,
> RTERM VARCHAR(80))
>AS
>DECLARE VARIABLE CNT INTEGER = 0;
>begin
> for
> select id, term from t
> where term = :tr and id <= :idin order by term descending, id
>descending

So they are coming out in DESCENDING order because that is the order
that you are sending them out!

If you want a SSP to produce a set in a particular order, you have to
write the procedure so that the first row output is the first one you
want...and so on. The way it is currently written, this will be e.g.

term1 id1
term1 id2
term2 id1
term2 id4
....

./heLen