ok, so i figure a table that's created as the result of an sp can't be
ordered by anything...but is there no way to simply REVERSE the order
of the entries in a result table once they've all been suspended?

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> 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
>