PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause


Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. when we search the book by book "title" the result set should follow some rule in sorting the data. The rules are defined below.

1- First, the data sort by the title but searching value name.
2- Second, a new release book means result set should sort by publish_date, but it is on second priority.

Answer: This scenario can be implemented using the CASE statement with ORDER BY clause, to understanding the problem and its solution I am creating a book_inventory table, see the following script.


create table book_inventory
(
       id serial not null,
       isbn text,
       title text,
       author text,
       publisher text,
       publish_date date
);
--Insert some data in above table
insert into book_inventory
(
       isbn,
       title,
       author,
       publisher,
       publish_date 
)
values
(
       '1234567890123',
       'great',
       'Dawn Brown',
       'Publisher1',
       '12/01/2002'::date
),
(
       '2345678901234',
       'The great',
       'Jane Austen',
       'pb2',
       '01/01/2002'::date
),
(
       '3456789012345',
       'The great job',
       'Harper Lee',
       'pb3',
       '28/03/2019'::date
),
(
       '4567890123456',
       'My great job',
       'Dilip Kumar Singh',
       'PB3',
       '12/02/2017'::date
),
(
       '1234567890143',
       'great',
       'Dilip',
       'pb4',
       '12/01/2010'::date
),
(
       '1234567890181',
       'heart',
       'DK',
       'pb5',
       '12/01/2003'::date
),
(
       '1234567896781',
       'article of great leaders',
       'DK',
       'pb6',
       '12/01/2005'::date
);


Now, we will write the queries to fetch data as per our requirement, and we will see the differences of result-set of the following PostgreSQL statements.

Step 1: In the following query, I will sort the data of the result-set by title value in ascending order. You will see the result-set is not as per our expectation.


select *
  from book_inventory
  where title like '%great%'
    order by title asc;


Result-set

Analysis of result-set: Our expectation- Title should be sort from the value 'great' but result-set sorted by alphabetically.

Step 2: Now, I will write the query to sort by column value.


select *
  from book_inventory
  where title like '%great%'
    order by
       case
       when title='great'
       then '1'
       else title end asc;


Result-set

Analysis of Result-set: You can see the result is sorted by the value "great" but it not sorted as per our expectations. For assumption rows 1 and 2 is the wrong to place, these should be in place of each other if sort by publish_date.

Step 3: Now, the following query will sort the records as per our expectation, means first, it should be sort with column particular column (title) value and with publish_date too, execute the following query it will return the expected result-set.


select *
  from book_inventory
  where title like '%great%'
    order by
       case
       when title='great'
       then '1'
       else title end asc
       ,publish_date desc;


Result-set


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...