I haven't been following this discussion as closely as I could have, but is
it also possible in Postgres to ORDER BY the ordinal position of the item in
the SELECT list, such as ORDER BY 1 or ORDER BY 3 , etc? That isn't as
descriptive as I like, but it worked in SQL Server, and I think it also
worked with calculated fields, GROUP BY, etc...
Thanks, David
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, April 08, 2005 3:00 PM
To: David
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Getting running totals
David <dbree@duo-county.com> writes:
> On Fri, Apr 08, 2005 at 01:33:53AM -0400, Tom Lane wrote:
>> SELECT to_char(date_trunc('month', t_date), 'Mon') AS "Month",
>> sum(transact.t_cost) AS "Month Ttl"
>> FROM transact
>> GROUP BY date_trunc('month', t_date)
>> ORDER BY date_trunc('month', t_date);
> That does it in a single command. I'm not sure I understand the full
> implications of what's occurring. It seems to me that ORDER BY can be
> sort of picky about what it will accept.
No pickier than the SELECT output list --- the rules are the same, in
fact. The reason that this is OK
SELECT to_char(date_trunc('month', t_date), 'Mon') AS "Month",
^^^^^^^^^^^^^^^^^^^^^^^^^^^
is that the parser sees that the part I underlined matches the GROUP BY
expression, and so it knows that the entire expression is well defined:
it will only have one value for each grouping value. The results of a
grouped query have to either have that property, or be aggregate
functions (which arrive at a single value per group too, of course).
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)