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
orderby add_date should indeed work. Since it appears that your add_date is a timestamp field (including time) it will
orderfirst 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
willstill 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
bea 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
>
>
