ORDER BY and LIMIT questions in EXCEPTs

Поиск
Список
Период
Сортировка
От Doug Fields
Тема ORDER BY and LIMIT questions in EXCEPTs
Дата
Msg-id 5.1.0.14.2.20021008140639.0292f850@pop.pexicom.com
обсуждение исходный текст
Ответы Re: ORDER BY and LIMIT questions in EXCEPTs
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: "Nigel J. Andrews"
Дата:
Сообщение: Re: Efficient date range search?
Следующее
От: Robert Treat
Дата:
Сообщение: Re: phpPgAdmin + PostgreSQL + authentication