make zero appear last in a list of ascending numbers

Question

I'm using SQLite in an Android application.
In all of my tables I have a default row with an index of 0 that holds default values for that table.
In most situations the default number for each field is 0 and that is the best number to use.
However, when I sort my data using an ORDER BY statement I want to have all of my zero value fields put at the end of the list as they're generally empty and using default information.
Excluding these fields in a where statement is unacceptable as I'm trying to sort information, not filter it.

Here's what I have so far:

select distinct item.name, epoch_date.epoch
from epoch_date, time
where (time.begin_date_id = epoch_date._id)
and (item.time_id = time._id)
order by epoch_date.epoch;

In my sandbox database, this returns something like:

"item 1", 0
"item 2", 0
"item 4", 0
.
.
.
"item 3",  1275350400
"item 42", 1275472800
"item 12", 1275472800

But what I want is:

"item 3",  1275350400
"item 42", 1275472800
"item 12", 1275476400
.
.
.
"item 1", 0
"item 2", 0
"item 4", 0

Answer 1

I'm not completely sure if SQLite allows case statements in "order by", but try something along these lines:

order by case epoch_date.epoch when 0 then 999999 else epoch_date.epoch end

Answer 2

Can you add an extra column to act as a filter, then sort by both columns?

select (...)epoch_date.epoch, case epoch_date.epoch when 0 then 1 else 0 as default
(...)
order by default,epoch_date.epoch

Answer 3

Try this:

ORDER BY
    CASE epoch_date.epoch WHEN 0 THEN 1 ELSE 0 END,
    epoch_date.epoch

I haven't tested in SQLite but works in many other databases so hopefully it should work in SQLite too.

Answer 4

Equivalent to the CASE statement suggestion, but shorter:

 ORDER BY epoch_date.epoch == 0, epoch_date.epoch

Answer 5

order by decode(epoch_date.epoch,0,null,epoch_date.epoch)

you can use something like this..since nulls are kept in the end in case of ascending order. provided you have decode function available in sqlite..or else you can just use a case statement instead.

Ravi Kumar