[SQL] Formatting Functions and Group By

[Terry Brick]

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 ')
order by
  to_char(myCol,'MM ')

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/

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Formatting Functions and Group By

[Terry Brick]

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 <[EMAIL PROTECTED]> 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 ')
> 
>   regards, tom lane





__
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway 
http://promotions.yahoo.com/design_giveaway/

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org