PostgreSQL: Sort result by DateTime, but the null value should be first or last



I want to sort my result by date time column in ascending order but null values ​​should come first or vice versa. Suppose I have a table employee and it has the column appraisal_date if I sorted the result set by appraisal_date in ASC/DESC order it should be the null value come FIRST/LAST.
         
PostgreSQL provides keywords such as "NULLS FIRST | LAST" that help find accurate results with "ORDER BY CLAUSE."

Illustration to get the accurate result-set sort by DateTime in ascending (ASC) order but null first


First I will create an employee table


CREATE TABLE employee
(
       emp_id int generated always as identity,
       name text,
       appraisal_date date
);

-- Here I will insert some records

INSERT INTO employee ( name, appraisal_date)
                     VALUES
                     ('Dilip', '27/04/2020'),
                     ('Mamta', '28/04/2020'),
                     ('Ashish', null),
                     ('Naina', '30/04/2020'),
                     ('Aradhya', '21/04/2020'),
                     ('Mukesh', null);


Sort Result null first



SELECT * FROM employee ORDER BY appraisal_date ASC NULLS FIRST;


Expected result



Illustration to get the accurate result-set sort by DateTime in descending (ASC) order but null first



SELECT * FROM employee ORDER BY appraisal_date DESC NULLS FIRST;


Expected Result


Illustration to get the accurate result-set sort by DateTime in descending (ASC) order but null first



SELECT * FROM employee ORDER BY appraisal_date ASC NULLS LAST;


Expected Result


Illustration to get the accurate result-set sort by DateTime in descending (ASC) order but null first



SELECT * FROM employee ORDER BY appraisal_date DESC NULLS LAST;


Expected Result


To support the query with an index, make it match:


CREATE INDEX appraisal_date_index ON employee (appraisal_date ASC NULLS FIRST);


OR


CREATE INDEX appraisal_date_index ON employee (appraisal_date DESC NULLS LAST);



No comments:

Post a Comment

Please do not enter any spam link in the comment box.

Related Posts

Fatal Error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails.

Problem: Sometimes, we get the following error when trying to make a connection in Rails.  FATAL ERROR: Peer authentication failed for us...