Indeed, it seems that I get the same result for a similar query.
I'm running version 7.3.4 on a rh 9 server.
Also: is the function date_part a function you wrote yourself? I get an error stating
that the function date_part("Unknown",date) is not recognized.
It maybe not a solution to the actual problem but you could try this:
save the date and the time in two seperate fields. I use a similar construction for
convenience.
Regards,
Stijn.
> Hello,
>
> Hm, doesn't work for me:
>
> [EMAIL PROTECTED] mydb=> select distinct date_part('year', uu.add_date),
> date_part('month', uu.add_date), date_part('day',
> uu.add_date) from uus inner join ui on uus.user_id=ui.id
> inner join
> uu on ui.id=uu.user_id where uus.subscriber_user_id=1 order by
> uu.add_date desc;
>
> ERROR: For SELECT DISTINCT, ORDER BY expressions must appear
> in target
> list
>
> I have this version of PostgreSQL installed: postgresql-7.3.4-3.rhl9
>
> Thanks,
> Otis
>
>
> --- Tom Lane <[EMAIL PROTECTED]> wrote:
> > <[EMAIL PROTECTED]> writes:
> > > I'd love to be able to do that, but I cannot just ORDER BY
> > uu.add_date,
> > > because I do not have uu.add_date in the SELECT part of the
> > statement.
> >
> > Sure you can. Back around SQL89 there was a restriction that ORDER
> > BY
> > values had to appear in the SELECT list as well, but no modern
> > database
> > has such a restriction anymore ...
> >
> > regards, tom lane
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
