How to get N record after S
The poster wanted to paginate query results for a web page — SELECT FIRST 50 works for page 1, but how to fetch rows 51-100, 101-150, etc.? Suggestions: dump the full result set into a temp table with a SERIAL column and then select ranges from it (slower first page, but easy forward/backward paging), or use SELECT FIRST 50 ... WHERE id NOT IN (SELECT FIRST n id ...). The latter was shown to fail with error 944 "Cannot use 'first' in this context", since FIRST isn't allowed in subqueries (or INTO clauses or SPL foreach). So the temp-table approach was the only working answer offered.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have a table with n100 record I want to show it in to webpage in some page In 1st page, i can use SELECT FIRST 50 FROM A And in 2nd, 3rd. how to get next 50 record after record 50, 100.
Not one of the easiest things to do! Best way I have heard of is to select all your results into a temporary table with a serial field then use this to select records 1-50 for page 1, records 51-100 for page 2, records 101-150 for page 3 etc. It does mean you first page may take rather longer to generate, having to retrieve the full results set first, but does allow you to page forward and backward through the results set using simple, quick queries from the temp table. Keith -> -----Original Message----- -> From: Vu Doan Hung [mailto:hung@hdcsoft.com] -> Sent: Wednesday, October 29, 2003 6:40 AM -> To: ids@iiug.org -> Subject: How to get N record after S [2093] -> -> -> I have a table with n100 record -> I want to show it in to webpage in some page -> In 1st page, i can use SELECT FIRST 50 FROM A -> And in 2nd, 3rd. how to get next 50 record after record 50, 100. -> -> -> -> ******************************************************************************** ** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ******************************************************************************** **
2nd
50
select first 50 from table where unique_id not in (select first 50 unique_id
from table)
3rd 50
select first 50 from table where unique_id not in (select first 100 unique_id
from table)
and so on.
-----Original Message-----
From: Vu Doan Hung [mailto:hung@hdcsoft.com]
Sent: Wednesday, October 29, 2003 1:40 AM
To: ids@iiug.org
Subject: How to get N record after S [2093]
I have a table with n100 record
I want to show it in to webpage in some page
In 1st page, i can use SELECT FIRST 50 FROM A
And in 2nd, 3rd. how to get next 50 record after record 50, 100.
It depends on your programming language and how you access the database. Please tell us more ? Yves -----Original Message----- From: nobody@ace.iiug.org [mailto:nobody@ace.iiug.org] Sent: woensdag 29 oktober 2003 7:40 To: ids@iiug.org Subject: How to get N record after S [2093] I have a table with n100 record I want to show it in to webpage in some page In 1st page, i can use SELECT FIRST 50 FROM A And in 2nd, 3rd. how to get next 50 record after record 50, 100. ------_=_NextPart_001_01C39E10.BA407E40 Content-Type: text/html; charset="iso-8859-1" <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1"> <META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2653.12"> <TITLE>RE: How to get N record after S [2093] </TITLE> </HEAD> <BODY> <P><FONT SIZE=2>It depends on your programming language and how you access the database.</FONT> <BR><FONT SIZE=2>Please tell us more ?</FONT> </P> <P><FONT SIZE=2>Yves</FONT> </P> <P><FONT SIZE=2>-----Original Message-----</FONT> <BR><FONT SIZE=2>From: nobody@ace.iiug.org [<A HREF="mailto:nobody@ace.iiug.org">mailto:nobody@ace.iiug.org</A>]</FONT> <BR><FONT SIZE=2>Sent: woensdag 29 oktober 2003 7:40</FONT> <BR><FONT SIZE=2>To: ids@iiug.org</FONT> <BR><FONT SIZE=2>Subject: How to get N record after S [2093] </FONT> </P> <BR> <P><FONT SIZE=2>I have a table with n100 record</FONT> <BR><FONT SIZE=2>I want to show it in to webpage in some page</FONT> <BR><FONT SIZE=2>In 1st page, i can use SELECT FIRST 50 FROM A</FONT> <BR><FONT SIZE=2>And in 2nd, 3rd. how to get next 50 record after record 50, 100.</FONT> <BR><FONT SIZE=2> </FONT> </P> </BODY> </HTML> ------_=_NextPart_001_01C39E10.BA407E40--
exactly this did'n work, if
you try it, you get the error:
944: Cannot use "first" in this context.This means a "first" subselect within "()" is not allowed.
Has anyone tried the commands before posting?
>
> 2nd 50
> select first 50 from table where unique_id not in (select first
> 50 unique_id from table)>
> 3rd 50
> select first 50 from table where unique_id not in (select first
> 100 unique_id from table)>
> and so on.
>
select firstcan be used in only normal query. Can not be used in:-
(a) into clause
(b) a subquery.
(c) in a SP inside foreach.
----- Original Message -----
From: "Andre Koppel" <akoppel@akso.de>
To: <ids@iiug.org>
Sent: Friday, October 31, 2003 06:10
Subject: AW: How to get N record after S [2114]
> exactly this did'n work, if you try it, you get the error:
> 944: Cannot use "first" in this context.> This means a "first" subselect within "()" is not allowed.
> Has anyone tried the commands before posting?
>
> >
> > 2nd 50
> > select first 50 from table where unique_id not in (select first
> > 50 unique_id from table)> >
> > 3rd 50
> > select first 50 from table where unique_id not in (select first
> > 100 unique_id from table)> >
> > and so on.
> >
>
>
>