Reputation: 514
Can anyone please help me on sorting records(Postgres Database) as intended?
Following is result of a query on one of my table that i have sorted by date part of the whole date (ORDER BY extract(DAY from cast(date as DATE))
)
NOTE :: All dates are in YYYY-MM-DD format
2016-03-01
2016-09-01
2016-08-01
2016-12-01
2016-01-01
2016-07-01
2016-11-01
2016-02-01
2016-06-01
2016-10-01
2016-04-01
2016-05-01
2016-07-22
2016-08-22
2016-10-22
2016-09-22
2016-11-22
2016-12-22
this is good as it sorts records as per the date part. But, what i want is, result should be such that all the records should be sorted based on date part so all the records from date 1 come before all the records from date 22 moreover it should sort those two sections i.e. records of date 1 and date 22 by month part too(If you look at the above result set, you will notice that it is sorted by date part but not by month part). So essentially the result that i want should look as follows
2016-01-01
2016-02-01
2016-03-01
2016-04-01
2016-05-01
2016-06-01
2016-07-01
2016-08-01
2016-09-01
2016-10-01
2016-11-01
2016-12-01
2016-07-22
2016-08-22
2016-09-22
2016-10-22
2016-11-22
2016-12-22
Upvotes: 0
Views: 234
Reputation: 522817
Use a two step ordering, and just add another sort within each block of days:
ORDER BY extract(DAY from cast(date as DATE)), -- first sort by day
date -- then sort by date
This ordering will place day 1 records first and others (i.e. day 22) records last. Within each block of 1/22 dates, the records are ordered ascending by date.
Upvotes: 1