[SQL] UNION and LIMIT issue

[Howard Smith]

in pgsql 8.2 using LIMIT with UNION is throwing errors...

table is
---

name   quantity
character varying integer
---
Banana   10
Cherry10
Apple 5
Persimmon  3
Mango2

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

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] UNION and LIMIT issue

[Tom Lane]

Howard Smith <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org