Re: Order by YYYY MM DD in reverse chrono order trouble

Hello,

Thank you for all your help, Stijn.
date_part is a standard PG function.
While not the most elegant, the DESC, DESC, DESC solution suggested the
other day works okay for me, so I think I'll use that for now.

Thanks again!
Otis

--- Stijn Vanroye <s(dot)vanroye(at)farcourier(dot)com> wrote:
> 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:
> >
> > me(at)[local] 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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > > <ogjunk-pgjedan(at)yahoo(dot)com> 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
> >