X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 09177D1B893 for ; Fri, 23 Apr 2004 04:53:53 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 05202-09 for ; Fri, 23 Apr 2004 04:53:52 -0300 (ADT) Received: from web12704.mail.yahoo.com (web12704.mail.yahoo.com [216.136.173.241]) by svr1.postgresql.org (Postfix) with SMTP id 00C30D1B498 for ; Fri, 23 Apr 2004 04:53:50 -0300 (ADT) Message-ID: <20040423075350.2957.qmail@web12704.mail.yahoo.com> Received: from [194.152.209.39] by web12704.mail.yahoo.com via HTTP; Fri, 23 Apr 2004 00:53:50 PDT Date: Fri, 23 Apr 2004 00:53:50 -0700 (PDT) From: Subject: Re: Order by YYYY MM DD in reverse chrono order trouble To: pgsql-sql@postgresql.org In-Reply-To: <71E201BE5E881C46811BA160694C5FCB0FA93A@fs1000.farcourier.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200404/248 X-Sequence-Number: 17623 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 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 wrote: > > > 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 > >