Hi,

I've got a stack of tasks to show in a list.
Every task has a timestamp X that may be NULL or a date. It contains
the date when this tasks should be done.
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

The point is, I like to do the skeduled tasks for today as planned. = (1)

Those allready lost appointments should not defer those today that are
still in time but I like to get them after the today-tasks in an order
where there is a chance that a nearer lost appointment might be still
rescued even though it's a bit late.
The dates longer back might be lost for good anyway so they can wait a
bit longer. = (2)

Provided I get through (1) and (2) I'd venture the unknown where there
wasn't a date until now. = (3)

Well, and future dates will be minded when their time is there. = (4)


For now I do this by having a sorting-column in the tasks-table that
gets updated in 4 steps where my application has to select every group
(1) - (4) then sequentially walk through the recordset and update the
sort-order-column by a counter.
Later I sort ascending by the sort-order-column.
It kind of works but I consider it ugly.

Could you provide a clever solution?


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

At 07:40 PM 5/12/07, Andreas wrote:
>I've got a stack of tasks to show in a list.
>Every task has a timestamp X that may be NULL or a date. It contains the
>date when this tasks should be done.
>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
>
>Could you provide a clever solution?


ORDER BY CASE WHEN X=today THEN 1 ELSE
CASE WHEN X<today THEN 2 ELSE
CASE WHEN X IS NULL THEN 3 ELSE
CASE WHEN X>today THEN 4 ELSE 5 END END END END


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 5/12/07, Frank Bax <fbax@sympatico.ca> wrote:
> At 07:40 PM 5/12/07, Andreas wrote:
> >I've got a stack of tasks to show in a list.
> >Every task has a timestamp X that may be NULL or a date. It contains the
> >date when this tasks should be done.
> >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
> >
> >Could you provide a clever solution?
>
>
> ORDER BY CASE WHEN X=today THEN 1 ELSE
> CASE WHEN X<today THEN 2 ELSE
> CASE WHEN X IS NULL THEN 3 ELSE
> CASE WHEN X>today THEN 4 ELSE 5 END END END END

Less verbose:

ORDER BY CASE
WHEN x = today THEN 1
WHEN x < today THEN 2
WHEN x IS NULL THEN 3
WHEN x > today THEN 4
ELSE 5
END

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

-----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=2529DF...9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFGRxZIvJuQZxSWSsgRAwrsAJ9HzZXzf3sQs0FVNSrhxN 5UpGhc+wCcDygQ
obe5G3b58+pXhqy4Ybh/OM8=
=rJpn
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match