How to Order by Date in PostgreSQL or Oracle

Problem:

You want to sort the rows by date.

Example 1:

The exam table has two columns, subject and exam_date.

You want to sort the rows by exam_date.

Solution:

SELECT *
FROM exam
ORDER BY exam_date;

The result looks like this (the rows are sorted in ascending order by exam_date):

Discussion:

Use the ORDER BY keyword and the name of the column by which you want to sort. This way, you'll sort the data in ascending order by this column. You could also use the ASC keyword to make it clear that the order is ascending (the earliest date is shown first, the latest date is shown last, etc.).

SELECT *
FROM exam
ORDER BY exam_date ASC;

If you'd like to see the latest date first and the earliest date last, you need to sort in descending order. Use the DESC keyword in this case.

SELECT *
FROM exam
ORDER BY exam_date DESC;

Note that in PostgreSQL and in Oracle, NULLs are displayed last when sorting in ascending order and first when sorting in descending order. Also, the rows with the same exam_date are displayed in random order (you may see Science second and Health third, or Health second and Science third).

Example 2:

The exam table has the following columns: subject, exam_year, exam_month, and exam_day. The months are given in names, not in numbers.

You want to sort the rows by the exam date.

Solution:

SELECT *
FROM exam
ORDER BY
  exam_year,
  EXTRACT(MONTH FROM TO_DATE(exam_month, 'Month')),
  exam_day;

The result looks like this (the rows are sorted in ascending order by exam_year, exam_month, and exam_date):

Discussion:

You need to order the rows by three values: the year, the month, and the day, but to have the correct order, you need to convert the month to a number ('January' to 1, 'February' to 2, etc.). Otherwise, you would see 'December' before 'January'. The TO_DATE(exam_month, 'Month') function converts the full month name to a date in the '0001-MM-01' format. For example, you get '0001-12-01' for December.

You can now use the EXTRACT(MONTH FROM date) function to extract the month from this date value. The month will be returned as a number.

Combining these two functions, you can get the month as a number using the following formula:

EXTRACT(MONTH FROM TO_DATE(exam_month, 'Month'))

To sort the rows by date, you need to sort by year, month, and day (in this order). If you'd like to see the latest exam first, you'll need to sort in descending order. To do this, you need to use a DESC keyword after each column in the ORDER BY clause.

SELECT *
FROM exam
ORDER BY
  exam_year DESC,
  EXTRACT(MONTH FROM TO_DATE(exam_month, 'Month')) DESC,
  exam_day DESC;