Skip site navigation (1) Skip section navigation (2)

Sorting distinct dates by year and month respectively

From: "Matt Arnilo S(dot) Baluyos (Mailing Lists)" <matt(dot)baluyos(dot)lists(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Sorting distinct dates by year and month respectively
Date: 2006-06-07 05:35:01
Message-ID: d1a6d7930606062235l50689fe8med114362df7cba8a@mail.gmail.com (view raw or flat)
Thread:
Lists: 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.

Responses

pgsql-novice by date

Next:From: Richard Broersma JrDate: 2006-06-07 05:43:31
Subject: Re: Sorting distinct dates by year and month respectively
Previous:From: Bryan IrvineDate: 2006-06-06 19:35:09
Subject: pgManage

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group