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@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)