| From: | Bryce Nesbitt <bryce1(at)obviously(dot)com> |
|---|---|
| To: | pgsql-sql(at)postgresql(dot)org |
| Subject: | Re: group by function, make SQL cleaner? |
| Date: | 2006-03-16 17:45:10 |
| Message-ID: | 4419A426.3030000@obviously.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-sql |
<!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(dot)1142489098(at)sss(dot)pgh(dot)pa(dot)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. 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 >= '2006-01-01' and endtime < '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>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 2.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Emi Lu | 2006-03-16 18:14:50 | About how to use "exception when ??? then " |
| Previous Message | rcolmegna@tiscali.it | 2006-03-16 16:58:28 | R: R: Re: schema inspection |