ORDER BY and LIMIT questions in EXCEPTs
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
It appears that postgres requires the ORDER BY or the LIMIT to be at the end
of the query. I think if you want the LIMIT, OFFSET, or ORDER BY to affect
anything other than the end result you'll have to use a subselect.
I could be doing something wrong, but I got a syntax error if I tried to put
those clauses in the middle of the query.
Regards,
Jeff Davis
Show quoted text
On Tuesday 08 October 2002 11:06 am, Doug Fields wrote:
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 offsetMany thanks,
Doug
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly