Re: [SQL] Funny date-sorting task
On 5/12/07, Frank Bax [EMAIL PROTECTED] 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 Xtoday THEN 2 ELSE CASE WHEN X IS NULL THEN 3 ELSE CASE WHEN Xtoday 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
Re: [SQL] 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 ELSE4 END, CASE WHEN X::date-now()::date 0 THEN now()-X ELSE X-now() END; - -- Greg Sabino Mullane [EMAIL PROTECTED] 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- ---(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
[SQL] Funny date-sorting task
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
Re: [SQL] Funny date-sorting task
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 Xtoday THEN 2 ELSE CASE WHEN X IS NULL THEN 3 ELSE CASE WHEN Xtoday 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
