pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Bryce Nesbitt <bryce1@obviously.com>
To: pgsql-sql@postgresql.org
Subject: Re: group by function, make SQL cleaner?
Date: Thu, 16 Mar 2006 09:45:10 -0800
Message-ID: <4419A426.3030000@obviously.com> (raw)
In-Reply-To: <12933.1142489098@sss.pgh.pa.us>
References: <4418F52F.4070804@obviously.com>
	<12933.1142489098@sss.pgh.pa.us>

<!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>

view thread (6+ messages)  latest in thread

Message-ID: <4419A426.3030000@obviously.com>
Permalink:  ../4419A426.3030000@obviously.com/
Also on:    postgresql.org/message-id/4419A426.3030000@obviously.com

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: bryce1@obviously.com
  Subject: Re: group by function, make SQL cleaner?
  In-Reply-To: <4419A426.3030000@obviously.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox