How to sort a MySQL table with null column values

Question

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

"sort by int column , then by varchar column" And have you actually tried that? — Patrick Q
– Patrick Q, Commented Apr 23, 2014 at 13:00

Ilesh Patel · Accepted Answer · 2014-04-23 13:13:42Z

2

Try This

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

edited Apr 23, 2014 at 13:13

answered Apr 23, 2014 at 13:02

Ilesh Patel

2,15516 silver badges27 bronze badges

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

Comments

Patrick Q · Accepted Answer · 2014-04-23 13:04:47Z

1

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

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

DEMO

answered Apr 23, 2014 at 13:04

Patrick Q

6,3942 gold badges28 silver badges34 bronze badges

Comments

Abhik Chakraborty · Accepted Answer · 2014-04-23 13:07:05Z

1

Another way is

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

DEMO

answered Apr 23, 2014 at 13:07

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Comments

Ronak Shah · Accepted Answer · 2014-04-23 13:01:09Z

0

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

answered Apr 23, 2014 at 13:01

Ronak Shah

1,5592 gold badges13 silver badges20 bronze badges

Comments

Strawberry · Accepted Answer · 2014-04-23 13:05:11Z

0

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

answered Apr 23, 2014 at 13:05

Strawberry

34k14 gold badges43 silver badges58 bronze badges

Collectives™ on Stack Overflow

How to sort a MySQL table with null column values

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related