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