Sorting null-data last in database query

i have an application where i order make a query to my database with an ORDER BY clause, it will order them in alphabetical order. i only have one small problem, it happens fairly often that one of the strings that the query is ordering by contains nothing (string="") when sorting in alphabetical order these get populated at 开发者_运维问答the top of the list infront om a,b,c... i plain and simple dont want this. after a lot of googling i found on an oracle forum that i should change the SORT BY part of the query to "SORT BY xxx ASC NULLS LAST" this caused a fatal error when querying.

how shall i go by fixing this seemingly small issue?

here is my query statement as is today.

public Cursor fetchAllDatesByTag() { return mdiktationsDb.rawQuery("SELECT " + KEY_DATEID + "," +" " + KEY_DATE + "," + " " + KEY_TIME + "," + " " + KEY_DICTTAG + "," + " " + KEY_DICTLISTIMAGE + " FROM " + DATABASE_TABLE + " ORDER BY " + KEY_DICTTAG + " ASC", null); }

---

use a CASE equivalent in your ORDER BY

Like

ORDER BY CASE column WHEN NULL THEN 1 ELSE 0 END, column

so then it orders by the nulls first, then the actual column.

EDIT: And if you want to filter ""s (blank strings) or whatever else, you can employ this same method... assigning a numeric value to it and sorting before the alphabetizing.

EDIT2:

....+ " ORDER BY CASE " + KEY_DICTTAG + "WHEN NULL THEN 1 ELSE 0 END, " + KEY_DICTTAG + " ASC"

---

Solution one:

ORDER BY foo NULLS LAST

and

ORDER BY foo NULLS FIRST

But this seems to work only with numeric columns :(

Solution two:

ORDER BY IF(ISNULL(my_field),1,0),my_field

which will create a "fake"-column that just consist 0 or 1, depending on if my_field is null or not null, and sort on that. the fields that are not null will come first. in a second step, SQL will sort my_field, like before.

---

How about adding a WHERE field IS NOT NULL. This way you shouldn't get any null values back from your query.