Postgres Pro
Downloads
Home   >   mailing lists

Thread: UNION and LIMIT issue

UNION and LIMIT issue

From
Howard Smith
Date:
in pgsql 8.2 using LIMIT with UNION is throwing errors...

table is
-------------------------------

name                       quantity
character varying     integer
-------------------------------
Banana                   10
Cherry                    10
Apple                     5
Persimmon              3
Mango                    2

the query :
     SELECT name,quantity FROM fruit limit 3     UNION              select name,CAST(SUM(quantity) as integer) from (
         select Cast('Other' as varchar) as name,quantity from fruit             limit ALL offset 3      ) w   group by
name

throws an error on the UNION. Removing the first 'Limit 3' fixes the query,
but i need the limit statement. Anybody seen this behavior?

-- 
Howard Smith
professional services engineer
Visual Mining Inc
15825 Shady Grove Road, Suite 20
Rockville, MD 20850
tel 301.795.2239  fax 301.947.8293

Re: UNION and LIMIT issue

From
Tom Lane
Date:
Howard Smith <hsmith@visualmining.com> writes:
>       SELECT name,quantity FROM fruit limit 3
>       UNION
>                select name,CAST(SUM(quantity) as integer) from (
>               select Cast('Other' as varchar) as name,quantity from fruit
>               limit ALL offset 3
>        ) w   group by name

You need parentheses:
(SELECT ... limit 3) UNION ...

ISTM that a LIMIT without an ORDER BY is a pretty bad idea, btw.
        regards, tom lane

Re: UNION and LIMIT issue

From
ljb
Date:
tgl@sss.pgh.pa.us wrote:
>...
> ISTM that a LIMIT without an ORDER BY is a pretty bad idea, btw.

Some databases actually output a warning when you do that, telling you your
result is non-deterministic. Which seems to me to be a little presumptuous...