Orbeon Forms community mailing list

sql result sets

Classic

List

Threaded

3 messages Options

Frank

sql result sets

Hi,
Is there a simple way to filter the result sets rows for an sql query.
Some technique to allow simple pagination of results - say a query
returns 100 rows and we want to return rows 20 to 30.

Thanks again,
franco

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

Erik Bruchez

Re: sql result sets

Administrator

Franco,

Every database may have a different way of doing this, but the standard
SQL I think consists in using LIMIT and OFFSET, e.g.:

SELECT * FROM employee LIMIT 10 OFFSET 10

This seems to be supported by MySQL, HSQLDB, and Postgres.

There is also TOP which can be used:

SELECT TOP 10 FROM employee

but this does not support an offset I think.

With Oracle, something like this should work:

SELECT * FROM employee WHERE ROWNUM BETWEEN 10 AND 20

You may have to put a subselect in place of "employee" in more complex
cases. There is a thread on this here which you may find useful:

http://www.petefreitag.com/item/451.cfm

-Erik

franco pace wrote:
> Hi,
> Is there a simple way to filter the result sets rows for an sql query.
> Some technique to allow simple pagination of results - say a query
> returns 100 rows and we want to return rows 20 to 30.
>
> Thanks again,
> franco
>

--
Orbeon Forms - Web Forms for the Enterprise Done the Right Way
http://www.orbeon.com/

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

Frank

Re: sql result sets

Hi Erik,
I am currently using MySQL and the limit/offset method. I was hoping
there was a technique that removed the DB specifics
out of the SQL and allowed the parameters to be passed into the SQL
processor.
I wasn't sure if OPS had such a feature that I couldn't find.
My main concern was with Oracle - I didn't think Oracle supported this.
Thanks for correcting me.

Regards,
Franco

Erik Bruchez wrote:

> Franco,
>
> Every database may have a different way of doing this, but the
> standard SQL I think consists in using LIMIT and OFFSET, e.g.:
>
> SELECT * FROM employee LIMIT 10 OFFSET 10
>
> This seems to be supported by MySQL, HSQLDB, and Postgres.
>
> There is also TOP which can be used:
>
> SELECT TOP 10 FROM employee
>
> but this does not support an offset I think.
>
> With Oracle, something like this should work:
>
> SELECT * FROM employee WHERE ROWNUM BETWEEN 10 AND 20
>
> You may have to put a subselect in place of "employee" in more complex
> cases. There is a thread on this here which you may find useful:
>
> http://www.petefreitag.com/item/451.cfm
>
> -Erik
>
> franco pace wrote:
>> Hi,
>> Is there a simple way to filter the result sets rows for an sql query.
>> Some technique to allow simple pagination of results - say a query
>> returns 100 rows and we want to return rows 20 to 30.
>>
>> Thanks again,
>> franco
>>
>
>
> ------------------------------------------------------------------------
>
>
> --
> You receive this message as a subscriber of the [hidden email] mailing list.
> To unsubscribe: mailto:[hidden email]
> For general help: mailto:[hidden email]?subject=help
> ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
>

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws