Thread: How to get "next" record in a recordset

How to get "next" record in a recordset

From
"Peter Alberer"
Date:

I am developing a testing application for the web where students can navigate thru multiple choice quizzes. Of course there should be a button to go to the next question (by alphabet, random, categories) My problem is how to get the “next” question of a certain recordset. For example:

 

select

lro.lr_object_id, lro.lr_title

from lr_objects lro, acs_rels

where object_id_one = '10524'

and rel_type= 'lr_concept_rel'

and object_id_two = lro.lr_object_id

and lro.is_active='t'

and restype = 'excs'

order by lro.lr_title;

 

lr_object_id |                  lr_title                 

--------------+--------------------------------------------

        14617 | Übungsaufgabe 2-31                        

        14614 | Übungsaufgabe 2-32                        

        13226 | Attacken von Free-Software-Programmen     

        13297 | Aussagen über Computerviren               

        13300 | Aussagen über Computerviren               

        13311 | Aussagen über Makros                      

        13316 | Aussagen über Virusprogramme              

        13235 | Aussagen im Zusammenhang mit Computerviren

        13400 | Computerviren                             

        13742 | Maßnahmen zum Schutz vor Computerviren    

        13768 | Mögliche Fälle von Computerviren          

        13774 | Möglichkeit eines Virenbefalls            

        13809 | PC-Programm: "Swiss Phönix"     

        13850 | Regelmäßige Virenüberprüfung              

        14071 | Warnung im Computermagazin                

 

I can not order by lr_object_id. If the lr_object_id of the current question is 13742 how can I retrieve the next lr_object_id with one sql-query? I have created a function (plpgsql) to get the next id but can it be done an easier way?

 

TIA, peter alberer

Re: How to get "next" record in a recordset

From
Martijn van Oosterhout
Date:
See furthur down...

On Tue, Sep 10, 2002 at 10:26:53AM +0200, Peter Alberer wrote:
> I am developing a testing application for the web where students can
> navigate thru multiple choice quizzes. Of course there should be a
> button to go to the next question (by alphabet, random, categories) My
> problem is how to get the “next” question of a certain recordset. For
> example:
>
> select
> lro.lr_object_id, lro.lr_title
> from lr_objects lro, acs_rels
> where object_id_one = '10524'
> and rel_type= 'lr_concept_rel'
> and object_id_two = lro.lr_object_id
> and lro.is_active='t'
> and restype = 'excs'
> order by lro.lr_title;

Something like:

select min(lr_object_id) from lr_objects where lr_object_id > 13742;

or maybe you can use limit.

> lr_object_id |                  lr_title
> --------------+--------------------------------------------
>         14617 | Übungsaufgabe 2-31
>         14614 | Übungsaufgabe 2-32
>         13226 | Attacken von Free-Software-Programmen
>         13297 | Aussagen über Computerviren
>         13300 | Aussagen über Computerviren
>         13311 | Aussagen über Makros
>         13316 | Aussagen über Virusprogramme
>         13235 | Aussagen im Zusammenhang mit Computerviren
>         13400 | Computerviren
>         13742 | Maßnahmen zum Schutz vor Computerviren
>         13768 | Mögliche Fälle von Computerviren
>         13774 | Möglichkeit eines Virenbefalls
>         13809 | PC-Programm: "Swiss Phönix"
>         13850 | Regelmäßige Virenüberprüfung
>         14071 | Warnung im Computermagazin
>
> I can not order by lr_object_id. If the lr_object_id of the current
> question is 13742 how can I retrieve the next lr_object_id with one
> sql-query? I have created a function (plpgsql) to get the next id but
> can it be done an easier way?
>
> TIA, peter alberer

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.