Postgres Pro
Downloads
Home   >   mailing lists

Thread: group by query?

group by query?

From
Christopher Kings-Lynne
Date:
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;

Re: group by query?

From
Christopher Kings-Lynne
Date:
> 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?

Ah, don't worry - I get it now :)

Chris