Sorting distinct dates by year and month respectively

Поиск
Список
Период
Сортировка
От Matt Arnilo S. Baluyos (Mailing Lists)
Тема Sorting distinct dates by year and month respectively
Дата
Msg-id d1a6d7930606062235l50689fe8med114362df7cba8a@mail.gmail.com
обсуждение исходный текст
Ответы Re: Sorting distinct dates by year and month respectively  (Richard Broersma Jr <rabroersma@yahoo.com>)
Re: Sorting distinct dates by year and month respectively  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Sorting distinct dates by year and month respectively  (Richard Broersma Jr <rabroersma@yahoo.com>)
Список pgsql-novice
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.

В списке pgsql-novice по дате отправления:

Предыдущее
От: "Bryan Irvine"
Дата:
Сообщение: pgManage
Следующее
От: Richard Broersma Jr
Дата:
Сообщение: Re: Sorting distinct dates by year and month respectively