Subject | order-by problem |
---|---|

Author | martinknappe |

Post date | 2006-06-02T14:03:27Z |

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

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