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.vanroye@farcourier.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@[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@sss.pgh.pa.us> wrote:
> > > <ogjunk-pgjedan@yahoo.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
> >