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.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...