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
Список
Дерево обсуждения
Sorting distinct dates by year and month respectively "Matt Arnilo S. Baluyos (Mailing Lists)" <matt.baluyos.lists@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 Richard Broersma Jr <rabroersma@yahoo.com>
Re: Sorting distinct dates by year and month respectively "Matt Arnilo S. Baluyos (Mailing Lists)" <matt.baluyos.lists@gmail.com>
Re: Sorting distinct dates by year and month respectively Tom Lane <tgl@sss.pgh.pa.us>
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
Дата:
FAQ