From: | Doug Fields <dfields-pg-general(at)pexicom(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | ORDER BY and LIMIT questions in EXCEPTs |
Date: | 2002-10-08 18:06:47 |
Message-ID: | 5.1.0.14.2.20021008140639.0292f850@pop.pexicom.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I have questions about how ORDER BY and LIMIT work with "EXCEPT" joined
queries.
Let's say I have a query like:
SELECT * FROM something
WHERE ...
ORDER BY field
EXCEPT
SELECT * FROM something
WHERE ...
(It's much faster than a self-join per my tests!)
Will the final output retain the order as specified by the "ORDER BY field"
clause?
Second question. Let's say I have a query like the above, but I insert a
"LIMIT limit OFFSET offset" clause. If I put the clause in the first SELECT
(before the EXCEPT), then I expect that the number of records returned will
be at most "limit" and possibly less due to the EXCEPT. Correct?
If I put the "LIMIT limit OFFSET offset" after the second SELECT, does it
apply to the whole integrated query, or just to the second SELECT clause?
Do I need to make this a subselect to make it apply to the whole query?
example:
SELECT * FROM (
SELECT * FROM something
WHERE ...
ORDER BY field
EXCEPT
SELECT * FROM something
WHERE ...
) AS a LIMIT limit OFFSET offset
Many thanks,
Doug
From | Date | Subject | |
---|---|---|---|
Next Message | Roj Niyogi | 2002-10-08 19:03:31 | Re: pg_dump command inside shell scripts |
Previous Message | Robert Treat | 2002-10-08 18:02:57 | Re: phpPgAdmin + PostgreSQL + authentication |