Обсуждение: Sorting distinct dates by year and month respectively
Sorting distinct dates by year and month respectively
От
"Matt Arnilo S. Baluyos (Mailing Lists)"
Дата:
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.
Re: Sorting distinct dates by year and month respectively
От
"Matt Arnilo S. Baluyos (Mailing Lists)"
Дата:
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.