CODEWITHSUNDEEP
mysql
Abhijith A C
Abhijith A C

Reputation: 530

How to sort a MySQL table with null column values

I have a table with following schema. I need to sort table by points, and if rows wiith img_link present (not null) come first also. Simply need to do is - sort by int column, then by varchar column.

+-----+--------+-----------+-----------+
| id  | name   | img_link  |  points   |
+-----+--------+-----------+-----------+
| 11  | smpl   | path.jpg  |  10       |
+-----+--------+-----------+-----------+
| 12  | main   |  null     |  20       |
+-----+--------+-----------+-----------+
| 13  | abcd   |  null     |  10       |
+-----+--------+-----------+-----------+
| 14  | xyls   | img_.png  |  10       |
+-----+--------+-----------+-----------+

Need a result like

+-----+
| id  |
+-----+
| 12  |
+-----+
| 11  |
+-----+
| 14  |
+-----+
| 13  |
+-----+

Upvotes: 0

Views: 117

Answers (5)

Ilesh Patel
Ilesh Patel

Reputation: 2155

Try This

SELECT * FROM table_name ORDER BY points DESC ,ISNULL(img_link), img_link

Upvotes: 2

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Another way is

select *
from table
order by `points` desc,
if(img_link = '' or img_link is null,1,0)

DEMO

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

SELECT * FROM my_table ORDER BY points DESC, img_link IS NULL, img_link DESC;

Upvotes: 0

Patrick Q
Patrick Q

Reputation: 6393

You basically wrote out in words exactly what you need to do.

SELECT id FROM someTable ORDER BY points DESC, img_link DESC;

DEMO

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 1549

try this:

select * from tabalename where img_link is not null order by point desc union select * from tabalename where img_link is null order by point desc

Upvotes: 0

Related Questions