union and limit

select * from (select * from table limit 3,0) as foo1
UNION ALL
select * (select * from table limit 3,0) as foo2

might work, but I did not try

----- Original Message -----
From: "Ben-Nes Michael" <miki@canaan.co.il>
To: "postgres" <pgsql-general@postgresql.org>
Sent: Tuesday, August 27, 2002 10:08 AM
Subject: [GENERAL] union and limit

Hi All

i tried to send the following querry:

select * from table limit 3,0 UNION ALL select * from table limit 3,0;

This querry returned me an error so i put each select in parenthesis and

it

On Tue, 27 Aug 2002, Ben-Nes Michael wrote:

Hi All

i tried to send the following querry:

select * from table limit 3,0 UNION ALL select * from table limit 3,0;

This querry returned me an error so i put each select in parenthesis and it
worked.

Is this the way to solve it ?
if it is, i think it should be in the docs.

If you read carefully the syntax for the SELECT from

http://developer.postgresql.org/docs/postgres/sql-select.html

you will see that the limit clause in the first select is not allowed
there. Don't forget the LIMIT applies to the entire result set not the last
select making up the union.

So in short, that is how it's supposed to work so there's no need to document
it.

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants

By adding the parantheses, you corrected the syntax problem. If the
result is what you want (i.e. taking the limited result of the two queries
and unite them) then you are done. You told PostgreSQL to treat the two
queries as subqueries.

Hegyvari Krisztian

"Ben-Nes Michael" <miki@canaan.co.il> 08/27/02 01:20pm >>>

On Tue, 27 Aug 2002, Ben-Nes Michael wrote:

Hi All

i tried to send the following querry:

select * from table limit 3,0 UNION ALL select * from table limit 3,0;

This querry returned me an error so i put each select in parenthesis and

it

worked.

Is this the way to solve it ?
if it is, i think it should be in the docs.

If you read carefully the syntax for the SELECT from

http://developer.postgresql.org/docs/postgres/sql-select.html

you will see that the limit clause in the first select is not allowed
there. Don't forget the LIMIT applies to the entire result set not the

last

select making up the union.

So in short, that is how it's supposed to work so there's no need to

document

it.

Yes but, if i add parenthesis around each select, it is working.
But i wonder if its ok to use parenthesis around each select.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Tue, 27 Aug 2002, Ben-Nes Michael wrote:

Hi All

i tried to send the following querry:

select * from table limit 3,0 UNION ALL select * from table limit 3,0;

This querry returned me an error so i put each select in parenthesis and

it

worked.

Is this the way to solve it ?
if it is, i think it should be in the docs.

If you read carefully the syntax for the SELECT from

http://developer.postgresql.org/docs/postgres/sql-select.html

you will see that the limit clause in the first select is not allowed
there. Don't forget the LIMIT applies to the entire result set not the

last

select making up the union.

So in short, that is how it's supposed to work so there's no need to

document

it.

Yes but, if i add parenthesis around each select, it is working.
But i wonder if its ok to use parenthesis around each select.

"Ben-Nes Michael" <miki@canaan.co.il> writes:

Is this the way to solve it ?
if it is, i think it should be in the docs.

It is; read the SELECT man page:

select

A select statement with all features except the ORDER BY, FOR
UPDATE, and LIMIT clauses (even those can be used when the select
is parenthesized).

regards, tom lane