Thread: Sorting distinct dates by year and month respectively

Hello everyone,

I'm trying to put some filtering feature in my application that sorts
some rows by year and then by month.

This is the initial result of running a query on the table to get the
unique dates:
gsin=> SELECT DISTINCT article_pubdate FROM articles ORDER BY
article_pubdate DESC;
 article_pubdate
-----------------
 2006-06-06
 2006-06-05
 2006-06-04
 2006-06-02
 2006-06-01

Now, I'd like to get only the year and month parts but I want them
ordered by year and then by month in ascending order. I'm using the
query below, but it doesn't order the results the way I want it.

gsin=> SELECT DISTINCT date_part('year', article_pubdate),
date_part('month', article_pubdate) FROM articles GROUP BY
date_part('year', article_pubdate), date_part('month',
article_pubdate) ORDER BY date_part('year', article_pubdate),
date_part('month', article_pubdate) DESC;
 date_part | date_part
-----------+-----------
      2002 |         5
      2005 |        12
      2005 |        11
      2005 |        10
      2005 |         9
      2005 |         8
      2005 |         7
      2005 |         6
      2005 |         5
      2005 |         4
      2006 |         6
      2006 |         5
      2006 |         4
      2006 |         3
      2006 |         2
      2006 |         1
(16 rows)

Can anyone help me figure out why this is so?

--
Stand before it and there is no beginning.
Follow it and there is no end.
Stay with the ancient Tao,
Move with the present.

> gsin=> SELECT DISTINCT date_part('year', article_pubdate),
> date_part('month', article_pubdate) FROM articles GROUP BY
> date_part('year', article_pubdate), date_part('month',
> article_pubdate) ORDER BY date_part('year', article_pubdate),
> date_part('month', article_pubdate) DESC;
>  date_part | date_part
> -----------+-----------
>       2002 |         5
> Can anyone help me figure out why this is so?


I believe that date_trunc will give you what you want.

mydb=> select date_trunc('month', tstamp) as month from process group by month order by month
limit 4;
        month
---------------------
 2005-10-01 00:00:00
 2006-01-01 00:00:00
 2006-02-01 00:00:00
 2006-04-01 00:00:00
(4 rows)

Regards,

Richard Broersma Jr.

"Matt Arnilo S. Baluyos (Mailing Lists)" <matt.baluyos.lists@gmail.com> writes:
> ... ORDER BY date_part('year', article_pubdate),
> date_part('month', article_pubdate) DESC;

The above means

    ... ORDER BY date_part('year', article_pubdate) ASC,
    date_part('month', article_pubdate) DESC;

You want

    ... ORDER BY date_part('year', article_pubdate) DESC,
    date_part('month', article_pubdate) DESC;

As noted by the other respondent, sorting on one date_trunc column is
probably the better way to do it, but I thought I'd point out the DESC
issue anyway.  A lot of people get that wrong.

            regards, tom lane

> Now, I'd like to get only the year and month parts but I want them
> ordered by year and then by month in ascending order. I'm using the
> query below, but it doesn't order the results the way I want it.
>
> gsin=> SELECT DISTINCT date_part('year', article_pubdate),
> date_part('month', article_pubdate) FROM articles GROUP BY
> date_part('year', article_pubdate), date_part('month',
> article_pubdate) ORDER BY date_part('year', article_pubdate),
> date_part('month', article_pubdate) DESC;
OOPS,

Sorry,

I previous email, did not really answer your question.

your year column is sorted ascending ( small to great).
your month column is sorted decending ( great to small).

you should specify asc or desc for both columns to insure the desired result.

Regards,

Richard Broersma Jr.

On 6/7/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> You want
>         ... ORDER BY date_part('year', article_pubdate) DESC,
>         date_part('month', article_pubdate) DESC;
> As noted by the other respondent, sorting on one date_trunc column is
> probably the better way to do it, but I thought I'd point out the DESC
> issue anyway.  A lot of people get that wrong.

On 6/7/06, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> you should specify asc or desc for both columns to insure the desired result.

Thanks Tom and Richard. It works as intended now. Learned something new today.

--
Stand before it and there is no beginning.
Follow it and there is no end.
Stay with the ancient Tao,
Move with the present.