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