pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stijn Vanroye <s.vanroye@farcourier.com>
To: ogjunk-pgjedan@yahoo.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Order by YYYY MM DD in reverse chrono order trouble
Date: Thu, 22 Apr 2004 12:47:10 +0200
Message-ID: <71E201BE5E881C46811BA160694C5FCB04672B@fs1000.farcourier.com> (raw)

Yes indeed, I seem to have misinterpreted that last one. My apologies.
The distinct solution I mentioned isn't going to solve it, you are absolutely right in your example.

To get back on track:
You don't have to use a field in the select part of you query to be able to use it in the order by clause. So using order by add_date should indeed work. Since it appears that your add_date is a timestamp field (including time) it will order first on the date part, and next on the time part.

E.g.:
2004-04-12 12:45:22
2004-04-12 09:55:25
2004-04-11 14:25:31
2004-04-11 11:11:25

Since you have a distinct only on the date parts of the timestamp this will make no difference in the end result, it will still be sorted correctly on the date.

but I'm wondering if a GROUP BY wouldn't also be a good solution? like this:
<---CODE---->
SELECT 
  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.x_id=1
GROUP BY
date_part('year', uu.add_date), date_part('month', uu.add_date), 
date_part('day',  uu.add_date);
<---END CODE---->
That way you don't need the DISTINCT part. The disadvantage however is that all the fields in the select must eighter be a part of the GROUP BY clause, or be used in an aggregate function.

Anyways: just descide whatever solution fits best for your needs.

Regards,

Stijn.

>Otis wrote:
> Hello,
> 
> But will this work even with my add_date column, which is a TIMESTAMP
> field?  Values in this column contain hours, minutes, 
> seconds, etc., so
> won't DISTINCT return multiple rows for add_dates that _are_ distinct,
> but are on the same day.
> 
> For example:
> 
>   2004 04 02 11:22:33.034
>   2004 04 02 22:33:44.055
> 
> Thanks,
> Otis
> 
> 
> --- Stijn Vanroye <s.vanroye@farcourier.com> wrote:
> > > Hello,
> > > 
> > > 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. 
> > > The reason I don't have it there is because I need 
> distinct YYYY MM
> > DD
> > > values back.
> > > Is there a trick that I could use to make this more elegant?
> > yes, you could use:
> > SELECT DISTINCT ON (field1, field2) field1, field3, FieldN 
> from table
> > 
> > Regards,
> > 
> > 
> > Stijn Vanroye
> 
> 



view thread (12+ messages)  latest in thread

Message-ID: <71E201BE5E881C46811BA160694C5FCB04672B@fs1000.farcourier.com>
Permalink:  ../71E201BE5E881C46811BA160694C5FCB04672B@fs1000.farcourier.com/
Also on:    postgresql.org/message-id/71E201BE5E881C46811BA160694C5FCB04672B@fs1000.farcourier.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: s.vanroye@farcourier.com, ogjunk-pgjedan@yahoo.com
  Subject: Re: Order by YYYY MM DD in reverse chrono order trouble
  In-Reply-To: <71E201BE5E881C46811BA160694C5FCB04672B@fs1000.farcourier.com>
  References: <71E201BE5E881C46811BA160694C5FCB04672B@fs1000.farcourier.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox