MySql: ORDER BY ASC, Nulls at the bottom

Question

NOTE My values were actually empty strings not null.

I want to sort my table Album ASC with nulls at the end. Every stack overflow post i found didn't work on my database.

Following doesn't work:

SELECT Album FROM songs ORDER BY isnull(Album,'1')
SELECT Album FROM songs ORDER BY Album NULLS LAST
SELECT Album FROM songs ORDER BY Album IS NULL
SELECT Album FROM songs ORDER BY -Album DESC
SELECT Album FROM songs ORDER BY ISNULL(Album), Album ASC
SELECT Album FROM songs ORDER BY ISNULL(Album)
SELECT Album FROM songs ORDER BY (CASE WHEN Album IS NULL then 1 ELSE 0 END),Album
SELECT Album from songs ORDER BY ISNULL(Album),Album;
SELECT Album FROM songs ORDER BY case when Album is null then 2 else 1 end, Album

how can i check if they are null? is there a difference between this "" and null ? — rez, Sep 28 '14 at 05:15
have a look at http://stackoverflow.com/questions/13542065/is-better-use-an-empty-value-as-a-or-as-null — M Khalid Junaid, Sep 28 '14 at 05:22

arif_suhail_123 · Accepted Answer · 2014-09-28T05:30:10.990

1

select album from songs ORDER BY CASE WHEN album IS NULL THEN 1 WHEN album= '' THEN 2 ELSE 3 END DESC, album ASC

It will take care of empty string as well

edited Sep 28 '14 at 05:30

answered Sep 28 '14 at 05:14

arif_suhail_123

2,509
2
12
16

Are you sure the values are NULL, not empty string? To sort empty strings last, specify "order by case when album = '' then 1 else 0 end, album – Dan Guzman Sep 28 '14 at 05:18
@DanGuzman-SQLServerMVP is there a difference between null and empty string ? how would i go through this if they were all empty ? – rez Sep 28 '14 at 05:19
@Arif_suhail_123 how will i sort it if my strings are empty and not null then ? – rez Sep 28 '14 at 05:21
select album ORDER BY CASE WHEN album IS NULL THEN 1 WHEN album= '' THEN 2 ELSE 3 END DESC, album ASC, sorry try this – arif_suhail_123 Sep 28 '14 at 05:24
this command gives me no results at all, both of them , you sure you are typing this right guys ? – rez Sep 28 '14 at 05:24

jmoreno · Answer 2 · 2014-09-28T05:40:31.633

1

Looking at your output, I believe you are confusing null and the empty string.

Select case when album is null then 'NULL'
        when album = '' then 'string.empty'
        Else album end as Album
From song
Order by case when album is null then 2
        when album = '' then 1
        Else 0 end, album

http://sqlfiddle.com/#!2/84541/3

edited Sep 28 '14 at 05:40

answered Sep 28 '14 at 05:22

jmoreno

12,752
4
60
91

@shiro, it had a typo, but that shouldn't result in no results, it should result in an error. If you don't get any result you have other problems. – jmoreno Sep 28 '14 at 05:43
its because im running sql queries on my custom php made site that doesn't recognize errors – rez Sep 28 '14 at 05:48

score 0 · Answer 3 · answered Sep 28 '14 at 06:15

0

Maybe consider a ORM for php? Doctrine or Propel, there are many flavors. Still creating SQL by hand, why?

But your NULL problem is not solved with an ORM indeed.

Why not set a constraint on this column:

NOT NULL DEFAULT ''

answered Sep 28 '14 at 06:15

kaasdude

1,336
2
8
13

MySql: ORDER BY ASC, Nulls at the bottom

3 Answers3