Thread: "order by" weirdness

Hello,

consider these three statements:

create table t (id integer, date datetime ) ;
select id from t group by id ;
select id from t group by id order by max(date) ;

Is it correct behavior that the second select returns one row where
as the first select returns zero rows? 

Thanks in advance for any help,
Hein

>Hello,

Dear Hein,

What are you trying to do with order by max(date)?

Order by date will give proper result.  For that
matter group by clause already does ordering, no need
to put a order by there.

Regards,

Dipankar

>consider these three statements:
>
>create table t (id integer, date datetime ) ;
>select id from t group by id ;
>select id from t group by id order by max(date) ;
>
>Is it correct behavior that the second select returns
>one row where

>as the first select returns zero rows? 

>Thanks in advance for any help,
>Hein




__________________________________________________
Do You Yahoo!?
Bid and sell for free at http://auctions.yahoo.com

>> consider these three statements:
>> 
>> create table t (id integer, date datetime ) ;
>> select id from t group by id ;
>> select id from t group by id order by max(date) ;
>> 
>> Is it correct behavior that the second select returns
>> one row whereas the first select returns zero rows? 

No, it is not.  (Although it took a few rounds of discussion in the
mailing lists to get everyone to agree on that... if you check the
archives you will find this issue has come up repeatedly.)

I have in fact just fixed this in current sources.  So, in 6.6 and
later, both statements will return zero rows if t is empty.

Note that with aggregates and no GROUP BY, you will get a row:

select count(id) from t ;
count
-----   0
(1 row)

select max(id) from t ;
max
---

(1 row)

which is correct behavior and will not change.
        regards, tom lane