Re: ORDER BY and LIMIT questions in EXCEPTs

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

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 offset
>
> Many 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