Re: Getting running totals

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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Friday, April 08, 2005 3:00 PM
To: David
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Getting running totals

David <dbree(at)duo-county(dot)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(at)postgresql(dot)org)