Sajeev C Sajeev C - 1 month ago 9
MySQL Question

mysql- sort list based on empty and null values as last in column

I have a table of users with basic details id, their names and profile photo (link to photo, actually)

CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL,
`userName` varchar(60) NOT NULL,
`photo` varchar(50) NULL,
`status` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=latin1;

INSERT INTO `users` (`id`, `userName`,`photo`, `status`) VALUES
(1, 'John', 'john.png',1),
(2, 'Jane', 'jane.png',1),
(3, 'Ali', '',1),

(6, 'Bruce', 'bruce.png',1),

(7, 'Martha', '',1),
(8, 'Sidney', '',1),
(10, 'Charlie', 'charlie.png',1),

(12, 'Elisa', '',1),
(14, 'Samantha', 'samantha.png',1),
(15, 'Hannah', 'hannah.png',1),

(16, 'Hannah', '',1),
(17, 'Kevin', 'kevin1.png',1),
(18, 'Kevin', 'kevin2.png',1),
(19, 'Ruth', '',1);


Not all users have profile picture. I would like to list these users in alphabetic order and also show users who have profile picture in front.

This is the query that I wrote:

select * from users ORDER BY photo DESC ;


It sorts users based on photo value but they are not being shown in alphabetic order.

Is it possible to list these users such that all users who have photos will appear top on the list with [userName] in alphabetic order?

SQL FIDDLE

Answer

You have empty vlaues here. So you can use this way.

SELECT * FROM users ORDER BY if(photo = '' or photo is null,1,0), userName

But in mysql has a method to order null values with (-).

SELECT * FROM users ORDER BY -photo DESC, userName ASC

Here photo column data order as null values last because of DESC order

OR

SELECT * FROM users ORDER BY
CASE WHEN photo IS NULL THEN 1 ELSE 0 END ASC, userName ASC
Comments