group by query?
От | Christopher Kings-Lynne |
---|---|
Тема | group by query? |
Дата | |
Msg-id | 4104B98F.7050006@familyhealth.com.au обсуждение исходный текст |
Ответы |
Re: group by query?
|
Список | pgsql-hackers |
In 7.4.3, my workmate came across the following situation he thought was odd. Basically, we want to know why the group by and order by in the outer query get their variables from the inner query and not the select parameters in the outer query? Chris -- shouldn't the first SELECT query perform the GROUP BY and ORDER BY upon the date -- in the outer SELECT as in the second query BEGIN; CREATE TABLE test_dates ( date TIMESTAMP ); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 0); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 1); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 2); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 3); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 4); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 5); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 6); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 7); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 8); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 9); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 10); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 11); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 12); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 13); SELECT date - interval '1 day' * EXTRACT(dow FROM date) AS date FROM (SELECT date FROM test_dates) AS sub GROUP BY date ORDERBY date; SELECT date - interval '1 day' * EXTRACT(dow FROM date) AS date FROM (SELECT date FROM test_dates) AS sub GROUP BY 1 ORDERBY 1; ROLLBACK;
В списке pgsql-hackers по дате отправления: