CODEWITHSUNDEEP
sqlpostgresqlsortingdatetime
Hannan
Hannan

Reputation: 514

Sorting of dates in order of date and then month such that records are sorted by date first and then by month

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

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

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

Related Questions