pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFormatting Functions and Group By
4+ messages / 3 participants
[nested] [flat]
* Formatting Functions and Group By
@ 2004-04-13 16:13 Terry Brick <terry_brick2000@yahoo.com>
0 siblings, 2 replies; 4+ messages in thread
From: Terry Brick @ 2004-04-13 16:13 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm porting a bunch of queries from MySQL to Postgres 7.4 and am having a problem with one
particular area. For example, a query like this works in MySQL:
select
to_char(myCol,'Mon YY')
from
myTable
group by
to_char(myCol,'MM YYYY')
order by
to_char(myCol,'MM YYYY')
Postgres will give me an error saying that "to_char(myCol,'Mon YY')" must be in the 'group by'. I
understand why that normally needs to be the case, but in theory, it would be ok here because they
are actually the same values (in the select and group by) but just formatted differently. I just
want the query to be grouped and ordered by month and year, but to be formatted differently in the
output.
Any ideas?
Thanks!!
__________________________________
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway
http://promotions.yahoo.com/design_giveaway/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Formatting Functions and Group By
@ 2004-04-13 16:45 Bruno Wolff III <bruno@wolff.to>
parent: Terry Brick <terry_brick2000@yahoo.com>
1 sibling, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2004-04-13 16:45 UTC (permalink / raw)
To: Terry Brick <terry_brick2000@yahoo.com>; +Cc: pgsql-sql
On Tue, Apr 13, 2004 at 09:13:05 -0700,
Terry Brick <terry_brick2000@yahoo.com> wrote:
> Hi,
> I'm porting a bunch of queries from MySQL to Postgres 7.4 and am having a problem with one
> particular area. For example, a query like this works in MySQL:
>
> select
> to_char(myCol,'Mon YY')
> from
> myTable
> group by
> to_char(myCol,'MM YYYY')
> order by
> to_char(myCol,'MM YYYY')
>
> Postgres will give me an error saying that "to_char(myCol,'Mon YY')" must be in the 'group by'. I
> understand why that normally needs to be the case, but in theory, it would be ok here because they
> are actually the same values (in the select and group by) but just formatted differently. I just
> want the query to be grouped and ordered by month and year, but to be formatted differently in the
> output.
>
> Any ideas?
You could do something like the following. It works in 7.4, but I am not
sure if it is completely standard.
area=> select to_char(date_trunc('month',current_date), 'MM YYYY')
area-> group by date_trunc('month',current_date);
to_char
---------
04 2004
(1 row)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Formatting Functions and Group By
@ 2004-04-13 17:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Terry Brick <terry_brick2000@yahoo.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2004-04-13 17:33 UTC (permalink / raw)
To: Terry Brick <terry_brick2000@yahoo.com>; +Cc: pgsql-sql
Terry Brick <terry_brick2000@yahoo.com> writes:
> I'm porting a bunch of queries from MySQL to Postgres 7.4 and am having a problem with one
> particular area. For example, a query like this works in MySQL:
> select
> to_char(myCol,'Mon YY')
> from
> myTable
> group by
> to_char(myCol,'MM YYYY')
> order by
> to_char(myCol,'MM YYYY')
Ah, good ol' MySQL :-( ... let the user do what he wants whether the
result is well defined or not ...
I'd suggest doing the grouping/ordering numerically rather than
textually. For instance,
select
to_char(date_trunc('month', myCol), 'Mon YY')
from
myTable
group by
date_trunc('month', myCol)
order by
date_trunc('month', myCol);
Now this assumes you really want a time-based ordering, which the quoted
example doesn't give --- you've got month sorting to the left of year,
is that really what you want? If it is then you'd need to go
group by
date_trunc('month', myCol)
order by
to_char(date_trunc('month', myCol), 'MM YYYY')
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Formatting Functions and Group By
@ 2004-04-13 17:49 Terry Brick <terry_brick2000@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Terry Brick @ 2004-04-13 17:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Thank you both for your responses. That's just what I needed.... and thanks for catching my
mistake Tom. And may I say that I am VERY happy to be moving to Postgres. The lack of a native
Win32 version was thing only thing holding us back from Postgres previously.
I think this is the only kind of query I will have had difficulty porting. I'm looking forward to
ditching MySQL and never looking back!! :)
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Ah, good ol' MySQL :-( ... let the user do what he wants whether the
> result is well defined or not ...
>
> I'd suggest doing the grouping/ordering numerically rather than
> textually. For instance,
>
> select
> to_char(date_trunc('month', myCol), 'Mon YY')
> from
> myTable
> group by
> date_trunc('month', myCol)
> order by
> date_trunc('month', myCol);
>
> Now this assumes you really want a time-based ordering, which the quoted
> example doesn't give --- you've got month sorting to the left of year,
> is that really what you want? If it is then you'd need to go
>
> group by
> date_trunc('month', myCol)
> order by
> to_char(date_trunc('month', myCol), 'MM YYYY')
>
> regards, tom lane
__________________________________
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway
http://promotions.yahoo.com/design_giveaway/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-04-13 17:49 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-13 16:13 Formatting Functions and Group By Terry Brick <terry_brick2000@yahoo.com>
2004-04-13 16:45 ` Bruno Wolff III <bruno@wolff.to>
2004-04-13 17:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-13 17:49 ` Terry Brick <terry_brick2000@yahoo.com>
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