Re: Funny date-sorting task

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> Every task has a timestamp  X  that may be NULL or a date. 
> ...
> Sometimes it has date and the time-part, too.
>
> The list should be like this:
> 1)   X  sometime today should come first in ascending time order.
> 2)   X  in the past should show up after (1) in descending order 
>         so that not so long back dates come first
> 3)   X  =  NULL
> 4)   X sometime in the future

Assuming you mean the literal sense of "today", and that future 
dates show with the least furthest away first:

SELECT * FROM yourtable
ORDER BY
CASE WHEN X::date = now()::date THEN 1 WHEN X::date < now()::date THEN 2 WHEN X IS NULL             THEN 3 ELSE
                  4
 
END,
CASE WHEN X::date-now()::date < 0 THEN now()-X ELSE                              X-now()
END;


- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200705130942
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFGRxZIvJuQZxSWSsgRAwrsAJ9HzZXzf3sQs0FVNSrhxN5UpGhc+wCcDygQ
obe5G3b58+pXhqy4Ybh/OM8=
=rJpn
-----END PGP SIGNATURE-----