pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUNION and LIMIT issue
3+ messages / 3 participants
[nested] [flat]
* UNION and LIMIT issue
@ 2007-07-19 22:00 Howard Smith <hsmith@visualmining.com>
0 siblings, 1 reply; 3+ messages in thread
From: Howard Smith @ 2007-07-19 22:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UNION and LIMIT issue
@ 2007-07-19 22:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Howard Smith <hsmith@visualmining.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2007-07-19 22:27 UTC (permalink / raw)
To: Howard Smith <hsmith@visualmining.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UNION and LIMIT issue
@ 2007-07-20 01:41 ljb <lbayuk@pobox.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: ljb @ 2007-07-20 01:41 UTC (permalink / raw)
To: pgsql-sql
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...
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-07-20 01:41 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-19 22:00 UNION and LIMIT issue Howard Smith <hsmith@visualmining.com>
2007-07-19 22:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-07-20 01:41 ` ljb <lbayuk@pobox.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox