Обсуждение: 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
worked.

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

Cheer

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
> worked.
>
> Is this the way to solve it ?
> if it is, i think it should be in the docs.
>
> Cheer
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.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.


--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants

> 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.

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

"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