On Tue, Apr 13, 2004 at 09:13:05 -0700, Terry Brick <[EMAIL PROTECTED]> 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) ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])