I think you may want to look into "Select first 20 ...."

Jason


martinknappe 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?
>
> thanx,
>
> martin
>
> ps: here's the code of the procedure:
>
> 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
> into :id, :rterm
> do
> begin
> cnt = :cnt + 1;
> suspend;
> if (:cnt = 20) then
> exit;
> end
> for
> select id, term from t
> where term < :tr order by term descending, id descending
> into :id, :rterm
> do
> begin
> cnt = :cnt + 1;
> suspend;
> if (:cnt = 20) then
> exit;
> end
> end
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>

--
The information transmitted herewith is sensitive information intended only for use to the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon, this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.