X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id BEB1B9DC827
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 16 Mar 2006 13:45:21 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 60212-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 16 Mar 2006 13:45:20 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from outbound.internet-mail-service.net
	(outbound6.internet-mail-service.net [216.240.47.197])
	by postgresql.org (Postfix) with ESMTP id 159869DC854
	for <pgsql-sql@postgresql.org>; Thu, 16 Mar 2006 13:45:18 -0400 (AST)
Received: from idiom.com (idiom.com [216.240.32.1])
	by outbound.internet-mail-service.net (Postfix) with ESMTP id
	832E1241A51
	for <pgsql-sql@postgresql.org>; Thu, 16 Mar 2006 09:45:18 -0800 (PST)
Received: from [192.168.1.109] (adsl-69-107-132-69.dsl.pltn13.pacbell.net
	[69.107.132.69])
	by idiom.com (8.12.11/8.12.11) with ESMTP id k2GHjIVO016777
	for <pgsql-sql@postgresql.org>; Thu, 16 Mar 2006 09:45:18 -0800 (PST)
	(envelope-from bryce1@obviously.com)
Message-ID: <4419A426.3030000@obviously.com>
Date: Thu, 16 Mar 2006 09:45:10 -0800
From: Bryce Nesbitt <bryce1@obviously.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.12) Gecko/20050921
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: group by function, make SQL cleaner?
References: <4418F52F.4070804@obviously.com> <12933.1142489098@sss.pgh.pa.us>
In-Reply-To: <12933.1142489098@sss.pgh.pa.us>
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.205 required=5 tests=[AWL=0.077,
	HTML_MESSAGE=0.001, HTML_TAG_EXIST_TBODY=0.126, MIME_HTML_ONLY=0.001]
X-Spam-Score: 0.205
X-Spam-Level: 
X-Archive-Number: 200603/235
X-Sequence-Number: 24581

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Tom Lane wrote:<br>
<blockquote cite="mid12933.1142489098@sss.pgh.pa.us" type="cite">
  <pre wrap="">In this particular case you could say

	... GROUP BY 1 ORDER BY 1;

"ORDER BY n" as a reference to the n'th SELECT output column is in the
SQL92 spec.  (IIRC they removed it in SQL99, but we still support it,
and I think most other DBMSes do too.)  "GROUP BY n" is *not* in any
version of the spec but we allow it anyway.  I'm not sure how common
that notation is.

  </pre>
</blockquote>
Thanks.&nbsp; Markus Bertheau also supplied this solution:<br>
<pre wrap="">SELECT enddate, count(*) FROM (
    SELECT date_trunc('day', endtime) AS enddate FROM eg_event WHERE
endtime &gt;= '2006-01-01'  and endtime &lt; '2006-03-01') as foo
GROUP BY enddate
ORDER BY enddate


It brings up a question though: is there any way in Postgres to set a "quirks" or "standards" mode.  Or get Postgres to log the compliance level of each command and command element used, e,g.:
</pre>
<table align="center" border="1" cellpadding="2" cellspacing="2"
 width="40%">
  <tbody>
    <tr>
      <td valign="top">ORDER BY n<br>
      </td>
      <td valign="top">SQL92<br>
      </td>
    </tr>
    <tr>
      <td valign="top">GROUP BY n<br>
      </td>
      <td valign="top">PSQL<br>
      </td>
    </tr>
    <tr>
      <td valign="top">(SELECT ...)<br>
      </td>
      <td valign="top">SQL99<br>
      </td>
    </tr>
    <tr>
      <td valign="top">SELECT<br>
      </td>
      <td valign="top">SQL99<br>
      </td>
    </tr>
    <tr>
      <td valign="top">count(...)<br>
      </td>
      <td valign="top">SQL99<br>
      </td>
    </tr>
    <tr>
      <td valign="top">date_trunc(string,...)<br>
      </td>
      <td valign="top">PQSL<br>
      </td>
    </tr>
  </tbody>
</table>
<pre wrap="">It is so easy to get lazy and start shrink-wrapping code to the database.  That's certainly why mysql SQL tends to be so non-portable....


</pre>
</body>
</html>