ORDER BY date and include records that don't have date?

Question

Is it possible to sort records by DATE but still include records that don't have date value? The latter would be placed at the end of the list.

This is a wordpress query but I figure my question is more appropriate here:

$sql = "SELECT * FROM $wpdb->postmeta";
$sql .= " INNER JOIN $wpdb->posts ON $wpdb->postmeta.post_id = $wpdb->posts.ID";
$sql .= " WHERE $wpdb->posts.post_type = 'task'";
$sql .= " AND $wpdb->posts.post_parent IN ( SELECT `ID` FROM $wpdb->posts WHERE `post_type` = 'task_list' AND `post_parent` IN ( SELECT `ID` FROM $wpdb->posts WHERE `post_status` = 'publish' AND `post_type` = 'project' ) )";
$sql .= " AND $wpdb->postmeta.post_id IN ( SELECT `post_id` FROM $wpdb->postmeta WHERE $wpdb->postmeta.meta_key = '_completed' AND $wpdb->postmeta.meta_value = '0' )";
$sql .= " AND $wpdb->postmeta.post_id IN ( SELECT `post_id` FROM $wpdb->postmeta WHERE $wpdb->postmeta.meta_key = '_assigned' AND $wpdb->postmeta.meta_value = '%s' )";
$sql .= " AND $wpdb->postmeta.meta_key = '_due'";
$sql .= " AND CAST( $wpdb->postmeta.meta_value AS DATE )";
$sql .= " GROUP BY $wpdb->posts.ID";
$sql .= " ORDER BY $wpdb->postmeta.meta_value ASC";

How do I modify it?

What is `AND CAST( $wpdb->postmeta.meta_value AS DATE )` supposed to achieve? — Neil, Apr 27 '13 at 12:07

score 1 · Accepted Answer · answered Apr 27 '13 at 02:13

This one worked:

$sql = "SELECT * FROM $wpdb->postmeta";
$sql .= " INNER JOIN $wpdb->posts ON $wpdb->postmeta.post_id = $wpdb->posts.ID";
$sql .= " WHERE $wpdb->posts.post_type = 'task'";
$sql .= " AND $wpdb->posts.post_parent IN ( SELECT `ID` FROM $wpdb->posts WHERE `post_type` = 'task_list' AND `post_parent` IN ( SELECT `ID` FROM $wpdb->posts WHERE `post_status` = 'publish' AND `post_type` = 'project' ) )";
$sql .= " AND $wpdb->postmeta.post_id IN ( SELECT `post_id` FROM $wpdb->postmeta WHERE $wpdb->postmeta.meta_key = '_completed' AND $wpdb->postmeta.meta_value = '0' )";
$sql .= " AND $wpdb->postmeta.post_id IN ( SELECT `post_id` FROM $wpdb->postmeta WHERE $wpdb->postmeta.meta_key = '_assigned' AND $wpdb->postmeta.meta_value = '%s' )";
$sql .= " AND $wpdb->postmeta.meta_key = '_due'";
$sql .= " GROUP BY $wpdb->posts.ID";
$sql .= " ORDER BY $wpdb->posts.ID ASC, CAST( $wpdb->postmeta.meta_value AS DATE ) ASC";

Thanks for the help!

score 1 · Answer 2 · answered Apr 27 '13 at 07:18

Simply drop the condition WHERE … CAST( $wpdb->postmeta.meta_value AS DATE ) since that removes NULL dates from the result. Without that, the rows will be included, and sorting will not change that. You can then re-insert that expression in the ORDER BY part to sort by date. NULL values will be sorted to the beginning, i.e. thy compare smaller than non-NULL values.

score 0 · Answer 3 · answered Apr 27 '13 at 02:00

0

try SELECT * FROM TABLE ORDER BY date

answered Apr 27 '13 at 02:00

Dan Baker

1,757
3
21
36

ORDER BY date and include records that don't have date?

3 Answers3