Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA57252 for ; Mon, 27 Sep 1999 09:47:46 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id JAA26128 for ; Mon, 27 Sep 1999 09:47:15 -0400 (EDT) To: pgsql-sql@hub.org Subject: Re: [SQL] Re: "order by" weirdness In-reply-to: Your message of Mon, 27 Sep 1999 00:52:32 -0700 (PDT) <19990927075232.4435.rocketmail@web117.yahoomail.com> Date: Mon, 27 Sep 1999 09:47:15 -0400 Message-ID: <26126.938440035@sss.pgh.pa.us> From: Tom Lane >> 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