MySql order by multiple columns sorting NULL entries at first

Question

I've a table as shown in the SQL Fiddle.

CREATE TABLE `test` (
  `id` varchar(250) NOT NULL,
  `name` varchar(512) NOT NULL,
  `description` text NOT NULL,
  `priority` int(11) DEFAULT NULL,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


 INSERT INTO `test` (`id`, `name`, `description`, `priority`, `created_date`) VALUES
('1', 'John', 'kasdjkaksdj', 3, '2018-07-12 07:37:25'),
('2', 'Doe', 'updations', 1, '2018-07-12 08:56:27'),
('3', 'Matt', 'sdasd', NULL, '2018-07-12 09:09:08'),
('4', 'Jiss', 'vghjgfghj', 1, '2018-07-13 12:33:19'),
('5', 'Noel', 'sdfsdf', NULL, '2018-07-13 12:33:29');

The priority column is NULL by default. User can specify any integer values to this column. I want to sort the table based on the priority asc and for rest of the records (which have NULL priority) sort by created_date asc.

I tried with the query

SELECT * FROM `test` ORDER BY priority, created_date

Result :

id    name    description priority    created_date

3     Matt    sdasd        (null)      2018-07-12T09:09:08Z

5     Noel    sdfsdf       (null)      2018-07-13T12:33:29Z

2     Doe     updations     1          2018-07-12T08:56:27Z

4     Jiss    vghjgfghj     1          2018-07-13T12:33:19Z

1     John    kasdjkaksdj   3          2018-07-12T07:37:25Z

But this is showing priorities with NULL values first followed by records sorted by priority.

Excepted result:

id    name    description priority    created_date

2     Doe     updations     1          2018-07-12T08:56:27Z

4     Jiss    vghjgfghj     1          2018-07-13T12:33:19Z

1     John    kasdjkaksdj   3          2018-07-12T07:37:25Z

3     Matt    sdasd        (null)      2018-07-12T09:09:08Z

5     Noel    sdfsdf       (null)      2018-07-13T12:33:29Z

Can anyone help me to find the correct query. Thanks in advance.

Praveen S · Accepted Answer · 2018-07-23 12:06:49Z

2

You can try this code

SELECT * FROM `test` ORDER BY ISNULL(priority) ,priority, created_date

DEMO

edited Jul 23, 2018 at 12:06

answered Jul 23, 2018 at 11:53

Praveen S

3954 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

demo test · Accepted Answer · 2018-07-23 11:58:55Z

0

Your query is working fine, but there is only one issue that is :

You need to just add "name" in after ORDER BY:

for reference : go to demo

answered Jul 23, 2018 at 11:58

demo test

11 bronze badge

Collectives™ on Stack Overflow

MySql order by multiple columns sorting NULL entries at first

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related