ORDER BY and LIMIT questions in EXCEPTs

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

Responses

Browse pgsql-general by date

  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