Post date and cck date: sort descending fails

I cannot get this to work correctly either. The weird thing is that the preview works right for me but when it's in a block it won't sort right.

Well, I can't reproduce this. If it works in preview but not in your block, did you actually save your view?

If you've saved your view and the view is still incorrect, can you paste the query the view is producing?

I have the same problem with CCK Date fields. As soon as a date field is added the system seems to automatically add an "ORDER BY ASC" to the SQL query

Example: This generates a page of node titles given a certain taxonomy term:

SELECT node.nid AS nid,    node.title AS node_title,    node.vid AS node_vid
 FROM drupal_node node   INNER JOIN drupal_term_node term_node_13 ON node.vid = term_node_13.vid AND term_node_13.tid = 13
 WHERE(term_node_13.tid = 13) AND (node.status <> 0)

Then add a date field:

SELECT node.nid AS nid,     node.title AS node_title,    node.vid AS node_vid,
   node_data_field_minutes_date.field_minutes_date_value AS node_data_field_minutes_date_field_minutes_date_value,
   node_data_field_minutes_date.nid AS node_data_field_minutes_date_nid,
   node.type AS node_type
 FROM drupal_node node 
 INNER JOIN drupal_term_node term_node_13 ON node.vid = term_node_13.vid AND term_node_13.tid = 13
 LEFT JOIN drupal_content_type_minutes node_data_field_minutes_date ON node.vid = node_data_field_minutes_date.vid
 WHERE (term_node_13.tid = 13) AND (node.status <> 0)
   ORDER BY node_data_field_minutes_date_field_minutes_date_value ASC

So who put that "ORDER BY" at the end? Then try to sort by descending date values:
SELECT ... ORDER BY node_data_field_minutes_date_field_minutes_date_value ASC, node_data_field_minutes_date_field_minutes_date_value DESC
...and we have conflicting ORDER BY instructions.

I'm also experiencing this issue: no matter how I set the sorting options (for 'post date' in my case), it always displays ascending. This is on a webhost running PHP 5.2.4 and MySQL 4.1.21 .
However on my personal server (WAMP, running PHP 5.2.6 and MySQL 5.0.51b), the very same site does the sorting thing properly. The main difference between the two servers is MySQL 4 vs MySQL 5 as far as I can tell.

I can't answer the question of 'who put the ORDER BY' there without at least seeing an export of the view.

I'm also experiencing this issue...

I use

WAMP
MySQL 5.051b
PHP 5.2.6
Apache 2.2.8

Please don't change the status of an issue if you have nothing useful to add to it. "Me too" posts are one thing, but I've already stated that I need more info and all you did was provide noise. Good job.

I have the same problem with a date module cck field; see attached export. We are on PHP 5.2.6, MySQL 5.0.27, D6.6, and latest available 6.x dev of views, cck, and date as of 7 Dec 2008.

The "Past events page" view sorts correctly in descending order by field_event_start_value, but the "Past events block" view does not, either live or in the preview.

I did some more testing. I discovered the following:
In most cases sorting nodes with views doesn't work at all (it just displays the default ordering: ascending on posted date, I think). But if you set the row style to 'fields', and add an imagefield sorting does work. I tried sorting on title, post date, nid.

So for me sorting nodes only works when they have an imagefield added to the fields. I also tried adding a textfield and a filefield, but those didn't work.

This is an export of a view that does sort properly:

$view = new view;
$view->name = 'test';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('fields', array(
  'title' => array(
    'label' => 'Titel',
    'link_to_node' => 0,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
  ),
  'field_picture_fid' => array(
    'label' => 'Afbeelding selecteren',
    'link_to_node' => 0,
    'label_type' => 'widget',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 1,
    'id' => 'field_picture_fid',
    'table' => 'node_data_field_picture',
    'field' => 'field_picture_fid',
    'relationship' => 'none',
  ),
));
$handler->override_option('sorts', array(
  'title' => array(
    'order' => 'ASC',
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
  'type_1' => array(
    'operator' => 'in',
    'value' => array(
      'page' => 'page',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type_1',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('row_options', array(
  'inline' => array(),
  'separator' => '',
));

and this one doesn't sort properly (only the imagefield is removed):

$view = new view;
$view->name = 'test';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('fields', array(
  'title' => array(
    'label' => 'Titel',
    'link_to_node' => 0,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
  ),
));
$handler->override_option('sorts', array(
  'title' => array(
    'order' => 'ASC',
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
  'type_1' => array(
    'operator' => 'in',
    'value' => array(
      'page' => 'page',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type_1',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('row_options', array(
  'inline' => array(),
  'separator' => '',
));

I hope this is usefull information.

Actually I might be experiencing a different issue here, since I do not use the date field.

Count me as one very surprised no one else has reported the issue. The fact that it has been reported for node post date, indicates to me it is not limited to CCK Date, but I am mostly confused as to why the sorting works in a page view for me, but not a block (yes, I am truncating all caches).

I'm not sure my problem is really views related. I initially thoudght so, but I recently discovered sorting is fine when not logged is as admin (uid 1).

I ran into something similar when cloning the Monthly Archive view.

If I set the Sort Criteria to 'desc', the preview would display correctly, but when the view was saved, the block was still sorted 'asc'.

I resolved this by opening Arguments > Node: Created year + month
and changing the 'Action to take if argument is not present' radio button to 'Summary, sorted descending'

So I guess the Arguments sort overrides the Sort sort.

Hope this helps!

Unfortunately, that workaround doesn't work for us as we're displaying the nodes in a table with other fields.

I've noticed that if we pass the ?order=myfield&sort=desc string in the URL, the display does sort in descending order in the block, so it's not that the mechanism doesn't work in blocks, it just doesn't seem to be getting called. Another export attached, after the upgrade to 6.x-2.2.

#13: Yes, summary sorting is unrelated to the normal sorting. In fact, when you have a summary active, the regular sorts are ignored.

#9 choster: I've examined your view. In the page view, the 'table' is set to override the sorts, and the default sort there is event date, descending. In the block view, the sorts are set to override, and the first sort is on event date and it's set to ascending. That appears to be the result you describe getting.

#10 Ylan: I importanted that view (it's quite simple) and it seems to work just fine on my test site. It sorts by title ascending, as it should. Maybe there is something else that's modifying the query after the fact? Your case sounds very odd, especially the part where it works when not uid 1 but not when you are uid 1.

We are experienced this as well but only with table display. The problem is that the table control defaults to "Default sort" on the Date, and that sort is being picked up as "ASC"... (ORDER BY node_data_field_date2_field_date2_value ASC, node_data_field_date_field_date_value DESC) I am not sure where the second part of that line is coming from, but it doesn't seem to have any affect.

It looks like there is a routine that to reading the default sort in plugins/views_plugin_style_table.inc, but if that is what this does, it isn't working. Since this seems to work perfectly when the table control Default sort is set to none, I think that this would be a good solution. If I can figure out how the "special None row" is generated (at the bottom of admin.inc), I'll submit a patch.

highermath: I don't understand what the problem is. The table is set to have the 'date' as the default sort, and the default sort is set to be 'ascending'. And that's exactly what you're getting.

Why do you think it shouldn't be ascending? That's what you've got in your settings...

There's a lot of stuff in this post now, and almost all of it I have determined to be user error. I'm going to close this issue. If you are still having problems and are confident you've checked all the things I have talked about (particularly pay attention to the 'summary' order because that is different from the 'sort' order) then open a new issue, but at this point this issue is too diluted to be useful to anyone anymore.

I have read all of the discussion here and I am facing a similar problem. I believe I have some more data to contribute though.

I have created several views, showing "all blogs archives" and every "blogger archives". For every view I have created a page display and a block. The page display works perfectly. The block doesn't. For the same view! This effect can be noticed here:

http://www.mundolinux.com.br/blog - the sorting for the block is wrong
http://www.mundolinux.com.br/arquivo - the sorting for the page is right

Both correspond to the same view. The SQL code is as follows:

SELECT DATE_FORMAT((FROM_UNIXTIME(node.created) + INTERVAL -7200 SECOND), '%Y%m') AS created_year_month,
COUNT(node.nid) AS num_records
FROM casa_node node
WHERE (node.status <> 0) AND (node.type in ('blog'))
GROUP BY created_year_month
ORDER BY created_year_month DESC

For the following sort criteria:

Sort criteria
Node: Post date desc

I hope I have added some light to this discussion.

Thanks!

Cesar

follow instructions

I had the same issue when using the archive block for my blog navigation. The preview and the page ordered descending like I wanted, but the block was ordered ascending, although as far as I could tell, all my settings were correct.

The info here didn't help me, so I just tried out changing some settings.
I had two filters in my view, ie 'Node: published yes' and 'node: type = blog post'. When removing the second filter, the block did order descending like I wanted. Then i added the second filter again, exactly the same, and now for some reason the block was ordered descending. Strange, since I didn't change anything in the end.

I'm a Drupal newbie, so I'm not sure what went wrong the first time, but this might be helpful for someone else...

I was initially confused by this as well. I assumed that the summary order was same as my declared sort order. And also assumed that block summary order was same as page summary order. I don't recall changing these initially so these must have been the presets for the 'monthly archive' view. Looking through the Arguments along with the Sort criteria for each display and making changes fixed the problem.

I think the confusion might be something of a UI issue, since I wasn't initially thinking that any sort criteria would be listed under Arguments but it makes sense looking back to Views 1 days.

Thanks for the explanation. Got the same problem of months ordering descending in page view and ascending in block view and this thread helped me to solve it. Indeed, the views interface is somewhat confusing in this matter.

I had the same issue at Views 3 & Drupal 7 , so i noticed that the sorting date (field) is pulled from the field you may have show... check the format if you expose end..or start date (if you have double) or if there is a created date in the sorting.

It might help:

Watch out for double sorting. Using Sort Criteria and using Default Sorts in the Fields can give odd results.
Read here: http://drupal.org/node/54459