Node Distinct is not working anymore

You have to select the option for the Node: Distinct filter (yes, even though there's only one).

In that screenshot it's giving you the error because you haven't selected the 'disctinct' option (to the right of the 'is' dropdown).

Weird. I can't reproduce this, either. I'm not sure what to say.

I can confirm this is happening when Node Distinct is added to an existing View with filters. Setting up a fresh copy of the View works correctly.

I too have the same issue. Prior to upgrade everything was fine. Now all the albums under my og_galleries show multiple rows, count equal to number of photos in each album. May be attached picture help - techs over here. Adding node: distinct didnt work. And also I realized that change in view type didn't reflect on the url as well. May be recreating of view would help. i shall let you know if it fixes the issue.

Well neither the recreation of view nor clearing view cache helped me out. I still see multiple entires rather than being grouped by. any other solutions?

I had the same problem that you had.
I was able to fix it by simply removing ALL filters, saving the view, then adding the distinct as the FIRST filter, than adding my other filters.
I hope this helps someone.

Ditto problem. The "distinct must have a value" error persists until I select "distinct" before saving but then the filter doesn't work and duplicate results are shown in the view. I tried deleting all filters, saving the file and then applying distinct as the first filter but it still doesn't work! Any other suggestions?

@merlin
this seems a serious problem. I have at least the following filters with the same problem:

"Node: Distinct"
"Node: New Content"

I've waited for a solution, before I really needed this functionality. It drives me a bit mad, because I've used the same version of views on another page and it worked. The basic difference of these pages is, that the one is an English page only, the other is a German translated page, where I use the locale and localization module but also many other modules, which might have caused the problem. I've updated to version 1.6, I've removed the translated strings with the locale string management, but that didn't solve the problem. Now I'm testing, if disabling all localization helps, reporting back later.

But in general, it seems to me, that some value is passed to the form validation or other core part that is involved, and this value is set to false, 0 or NULL and that causes the problem.

Is this form fully validated by the views module or are there drupal core functions involved that handle the validation of the according fields?

I desperately need this functionality and want to help solve this problem. Especially "Has New Content" is one of the most important filters for me.

I'm trying to gather more information...

I saw that you already solved such a problem:
http://drupal.org/node/134703

What did you do the last time?

MORE INFO:
After disabling locale module I was able to save the view including "Node: Distinct" filter!!!
I was right, this is related to other modules and validation. So what is happening when this string gets translated? Is this an issue for the locale module, some core part or is it a compatibility issue that needs to be handled in views?

I'm using the latest version, so changing version, because the latest version didn't solve the problem

@the others
Can you verify this? Do you also use the LOCALE module?

I thought I've the same bug, but now I'm not sure anymore.

I tried to disable my locale module and put again Node: Distinct filter, but it didn't work.

So I activated the dev SQL log display, and there's something strange for me :

The query was :

SELECT DISTINCT (
node.nid
), term_data.weight AS term_data_weight, term_data.name AS term_data_name, node.title AS node_title, node.changed AS node_changed, node_data_field_description_courte.field_description_courte_value AS node_data_field_description_courte_field_description_courte_value, node_data_field_description_courte.field_description_courte_format AS node_data_field_description_courte_field_description_courte_format
FROM node node
LEFT JOIN term_node term_node ON node.nid = term_node.nid
LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
LEFT JOIN content_type_fiche_plume node_data_field_description_courte ON node.vid = node_data_field_description_courte.vid
WHERE (
node.type
IN (
'fiche_plume'
)
)
GROUP BY node.nid, term_data_weight, term_data_name
ORDER BY term_data_weight ASC , term_data_name ASC , node_title ASC
LIMIT 0 , 10

and the result was :

nid 	term_data_weight 	term_data_name 	node_title 	node_changed 	node_data_field_description_courte_field_description_courte_value 	node_data_field_description_courte_field_description_courte_format
46 	0 	administration 	CUPS 	1187860666 	Serveur d'impression pour Linux, Unix et MacOS X 	4
10 	0 	administration 	LaTeX 	1187773119 	Système logiciel de composition de documents 	4
15 	0 	administration 	openLDAP 	1185199583 	  	0
14 	0 	administration 	PhpMyVisites 	1180707467 	  	0
8 	0 	bureautique 	FeedReader 	1187350826 	lecteur de flux rss 	4
17 	0 	bureautique 	Thunderbird 	1187350876 	client mail multiplateformes 	4
51 	0 	dév 	VIM 	1187860344 	éditeur 	4
46 	0 	développement logiciel 	CUPS 	1187860666 	Serveur d'impression pour Linux, Unix et MacOS X 	4
14 	0 	développement logiciel 	PhpMyVisites 	1180707467 	  	0
51 	0 	développement logiciel 	VIM 	1187860344 	éditeur 0

The problem is that I have multiple entries (like nid 14 for "PhpMyVisites") different "term_data" associated, so the DISTINCT cause doesn't work as expected. The first one ("administration") is from a taxonomy vocabulary present in a "Field" of my View, but the second one ("developpement logiciel") is from another vocabulary (not present as a Field of my View (wich I don't expect). The weird thing is that I have other terms in the second vocabulary other than "developpement logiciel", but they doesn't appear.

Did I missed something? Isn't the query supposed to get only the values of the data required in defined "Fields" values of the Views ? Why the View module returns terms from another taxonomy vocabulary ?

Hope this could help... (If you need more infos, just ask)

pyg

same problem - i want to display table with list of users.... i can see every user 6 times, and when adding distinct filter, it says "Node: Distinct must have a value!"

There is a problem with the SQL statement.

This returns duplicates, if there is more than one term for a node:

GROUP BY node.nid, term_data_weight, term_data_name

This gets rid of duplicates:

GROUP BY node.nid

I had the same problem. Disabling og and re-enabling it solved this for me. I don't think it is og related, however, but the view cache seems to be the issue. Unfortunately, now that it works I can't reproduce it anymore.

May I ask you (jhm) to be more specific.
What do you mean by "og"?
Did you also work on the user list?
Could you give a short summary what steps needs to be done to get "distinct" working?

That sql query in #16 does look funky -- the group by clause would cause dupes to be returned as mentioned above in #18. I too am seeing that nodes with multiple terms cause duplicate related (by taxonomy) nodes to come back via views. Any fix for this on the horizon? It pretty much means that node distinct doesn't work since many nodes will have multiple terms. Thanks for any info.

Bump. Any confirmation of this?

I think this is a dupe of this ...

http://drupal.org/node/256148

Sorry to have bumped on my last post.

.

Drupal 5.10
MySQL database 5.0.62
PHP 5.2.6

Keep getting the same message. In my drupal log:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''')

ORDER BY node_data_field_loggen_fart_field_loggen_fart_value DESC LIMIT 0, ' at line 1 query: SELECT node.nid, node_data_field_loggen_fart.field_loggen_fart_value AS node_data_field_loggen_fart_field_loggen_fart_value, profile_real_name.value AS profile_real_name_value FROM node node LEFT JOIN content_type_loggen node_data_field_loggen_fart ON node.vid = node_data_field_loggen_fart.vid INNER JOIN users users ON node.uid = users.uid LEFT JOIN profile_values profile_real_name ON users.uid = profile_real_name.uid AND profile_real_name.fid = '1' WHERE (node.status = '1') AND (node.type IN ('loggen')) AND (node_data_field_loggen_fart.field_loggen_fart_value > '1') AND (. '') ORDER BY node_data_field_loggen_fart_field_loggen_fart_value DESC LIMIT 0, 5 in /drupal/includes/database.mysqli.inc on line 154.

I am using

Drupal 5.10
Views 1.6
Calendar 2.3
Date 2.3
Do NOT have Locale module enabled
Do NOT have OG installed

I have created nodes with multiple dates. Even with Node Distinct enabled without errors, I get nodes listed multiple times based on the dates.

I tried to remove all filters from the view, save the filter, then add the filter conditions with Node Distinct as the first condition. But I still get the same result.

Any insight would be appreciated!

Yet another confirmation that this is happening. A temporary fix for my specific instance of this problem was to remove the single sort criteria ("Random") from the view. This fix is less than ideal, since it breaks what I'm trying to do with the view (at least the block it exposes), but it makes things render properly at least (we were chasing down a rendering bug too).

If I find a way to fix this while enabling sorting, I'll whip up a patch and submit it.

Okay, here's a first stab at a patch. This fixes the Node: Random case at least (for sort criteria, that is), and doesn't appear to break anything else. rand() should not be added to the SELECT clause columns list (as it is prior to this patch); it should be added solely as an ORDER BY clause, which this patch does. It does this by adding a separate function to add "rand()" (for MySQL) or "random()" (for PostgreSQL) to the $this->orderby[] array if it's not already present (calling the new function twice for a single query won't generate a duplicate clause).

The correct fix for this is probably to entirely refactor the add_orderby() function to not automatically add fields in other places unless it's absolutely necessary. I get it that the function is handy for instances where you're sorting by a field you're not filtering on, but there's probably a better way to implement this.

If anyone wants to build on this to create a more generic patch, go for it :)

rand() should not be added to the SELECT clause columns
list (as it is prior to this patch); it should be added solely as an
ORDER BY clause,

Incorrect. All ORDER BY clauses must be added to the SELECT clause in ANSI SQL.

I stand corrected. The query generated by the unpatched module, while possibly ANSI SQL compliant, does not return the desired/expected results. Some refactoring is needed to make it behave properly.

ANSI compliance aside, the patch fixes the behavior for MySQL when using the Random sort order and Node: Distinct. The fix was produced to scratch an itch one of my clients had, and it may be of limited scope and usability (especially if the result is a malformed query that "just happens to work").

I am open to suggestions how to adjust the generated query so that it works *and* maintains ANSI compliance.

This is also happening for me, especially concerning random sorting of data. Distinct will not work if I sort randomly. I submitted a support request here: http://drupal.org/node/317739,

Is this patch working? I'm about to try it out but something about standards below and Merlin seemed to disagree.

Is this thing good to go?

Thanks.

Okay, I have tested the patch and it does not work. I uploaded it to my views module folder, and the modules folder (I never know which one to move it to so I just did both), set node: distinct filter, and sort criteria to random, and out of my block of four, 3 out of the 4 are the same node.

I wish I was a developer so I could help more, but all I can do is test it.

Thank you.

Subscribing.

This issue has also cropped up for me for a view that specifies a random sort order. The only nodes that seem to be repeated are the ones that have multiple taxonomy terms specified for them.

Since Views won't be upgraded to 2 in Drupal 5, I'd love to figure out a solution!

Let me know if I can test things.

Erik.

Hello,

I am having trouble with this same issues. Has anyone had any luck with this patch? It's important for me to get up and running to be able to sort my nodes without repeats.

Subscribing.

Ok, After reading more on patches I think I didn't quite get the grasp of how to install them, for some dumb reason I thought I just had to move them into my views directory. Now that I've figured out how to patch, there is a glaring problem which I want to make sure of before I go ahead and risk screwing up my views module. In the patch there is a path for views_node.inc, diff -Naur /home/harrypotter/views_module/views/modules/views_node.inc views/modules/views_node.inc.

What is that? Am I supposed to replace the path to my views_node.inc? Common sense would say I should replace that with my own path, it would make sense but why wasn't this outlined somewhere or commented? How am I or anyone else supposed to know what to do with that patch if they haven't done it before? I love drupal. I love views, and I think open-source web-applications are the greatest thing since sliced bread. This stuff is truly amazing, and so are the people who make it a reality, but being on this CMS and these forums for a few months I can smell the hatred of the 'noob' seeping out of my keyboard, and as such the standard punishment for the 'noob' not knowing everything is to ignore all his questions with extreme prejudice. While its quite true that most 'noobs' could save experienced developers the time of answering hundreds of questions that wouldn't have been asked had they only RTFM'd (which itself isn't always easy to find), but I mean cmon. How could someone who has little patching experience, like me, be expected to follow a set of instructions to make something work if the instructions aren't outlined? I didn't see anything on the drupal patching guidelines that gave me a definitive answer on this.

Please don't take me as being ungrateful or unhappy with the hard work people do on these modules, but like me, I'm sure all of you are very good at different areas which you weren't good at in some earlier point in your life and you needed to learn, and it was made easier by people that knew before you and took the time to make communal knowledge more communal.

Thank you.

whether rightly or wrongly, I decided to apply the patch anyway and ignore the path issue mentioned above, and I got this error:

Fatal error: Cannot redeclare _views_query::add_orderbyrandom() in /home/*****/public_html/modules/views/views_query.inc on line 438.

So I open up my code editor, and it seems that function add_orderbyrandom() { is repeated twice. I delete the duplicate, put it on my server, and the page loads but looks hella funky so I switched back.

Unless I'm doing something wrong, I don't think this patch works.

Ok, I think I *might* have found a solution.

If Node: Distinct is the FIRST filter in the filter option, and sort: criteria is set to random, I am not showing any repeats of nodes, regardless of taxonomy tags. So far so good... I'll post this in the duplicate forum as well.

FYI - Marked this item as a duplicate issue: http://drupal.org/node/317739
(contains little information, not sure if it is shedding useful extra light on this or not)

still repeated nodes, even with "Node: Distinct is the FIRST filter in the filter option, and sort: criteria is set to random"

Try deleting the view, remaking it, and putting "node: distinct" as the first filter before others are added.

subscribing

If this boils down to the configuration and order of things, plus perhaps rebuilding a malfunctioning view, then it is perhaps a support request rather than a bug report?

I'm also confirming that my view which was working before, was broken when I updated to 5.x-1.6. After the upgrade, my view had the distinct filter as the first filter, sort order set to random, and showed duplicate nodes in the result. (Removing the sort order does remove the duplicate nodes from the view)

FAILS: Deleting and adding back the distinct filter did not solve the problem.

FAILS: Deleting all the filters, and adding them back with distinct first also did not solve the problem.

FAILS: Cloning the broken view did not solve the problem (no surprise here).

FAILS: Rebuilding the broken view from scratch (worked for others?).

I'd also love to see a fix for this get pushed through. Is there anything I can do to help?
Jen

Subscribing.

I can also confirm this issue. Mine is being caused by a view i have created that displays events via the cck date field. I have one view that displays them in order by time, and one that displays randomly, and neither except the node distinct field. Even stranger, I have a different view that i use on the admin side of the site, and it works just fine.

This View works:

  $view = new stdClass();
  $view->name = 'admin_events';
  $view->description = 'Administer events';
  $view->access = array (
  0 => '3',
  1 => '7',
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = 'Events';
  $view->page_header = '<style type="text/css">
#edit-filter0 {
  width: 50px;
}
#edit-filter1 {
  width: 125px;
}
#edit-filter3 {
  width: 50px;
}
</style>';
  $view->page_header_format = '3';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = '';
  $view->page_empty_format = '1';
  $view->page_type = 'action_table';
  $view->url = 'admin/events';
  $view->use_pager = TRUE;
  $view->nodes_per_page = '25';
  $view->sort = array (
    array (
      'tablename' => 'node_data_field_event_date',
      'field' => 'field_event_date_value',
      'sortorder' => 'ASC',
      'options' => '',
    ),
  );
  $view->argument = array (
  );
  $view->field = array (
    array (
      'tablename' => 'node',
      'field' => 'delete',
      'label' => '',
      'handler' => 'views_handler_node_delete_destination',
    ),
    array (
      'tablename' => 'node',
      'field' => 'edit',
      'label' => '',
      'handler' => 'views_handler_node_edit_destination',
    ),
    array (
      'tablename' => 'node_data_field_event_date',
      'field' => 'field_event_date_value',
      'label' => 'Event Date',
      'handler' => 'date_views_field_handler_first',
      'sortable' => '1',
      'options' => 'short',
    ),
    array (
      'tablename' => 'node',
      'field' => 'title',
      'label' => 'Title',
      'handler' => 'views_handler_field_nodelink',
      'sortable' => '1',
      'options' => 'link',
    ),
    array (
      'tablename' => 'node_data_field_event_ref_venue',
      'field' => 'field_event_ref_venue_nid',
      'label' => 'Venue',
      'handler' => 'content_views_field_handler_group',
      'options' => 'plain',
    ),
    array (
      'tablename' => 'term_node_11',
      'field' => 'name',
      'label' => 'Category',
      'options' => 'nolink',
    ),
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => 'event',
),
    ),
    array (
      'tablename' => 'node',
      'field' => 'title',
      'operator' => 'word',
      'options' => '',
      'value' => '',
    ),
    array (
      'tablename' => 'term_node_11',
      'field' => 'tid',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => '120',
),
    ),
    array (
      'tablename' => 'node_data_field_event_ref_venue',
      'field' => 'field_event_ref_venue_nid_default',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => '0',
),
    ),
    array (
      'tablename' => 'node_data_field_event_feature',
      'field' => 'field_event_feature_value_default',
      'operator' => 'AND',
      'options' => '',
      'value' => array (
  0 => 'No',
),
    ),
    array (
      'tablename' => 'node_data_field_event_date',
      'field' => 'field_event_date_value_default',
      'operator' => '>=',
      'options' => 'now',
      'value' => '',
    ),
    array (
      'tablename' => 'node',
      'field' => 'distinct',
      'operator' => '=',
      'options' => '',
      'value' => array (
  0 => 'distinct',
),
    ),
  );
  $view->exposed_filter = array (
    array (
      'tablename' => 'node_data_field_event_date',
      'field' => 'field_event_date_value_default',
      'label' => 'Date',
      'optional' => '1',
      'is_default' => '1',
      'operator' => '1',
      'single' => '1',
    ),
    array (
      'tablename' => 'node',
      'field' => 'title',
      'label' => 'Title',
      'optional' => '1',
      'is_default' => '0',
      'operator' => '1',
      'single' => '1',
    ),
    array (
      'tablename' => 'term_node_11',
      'field' => 'tid',
      'label' => 'Categories',
      'optional' => '1',
      'is_default' => '0',
      'operator' => '1',
      'single' => '1',
    ),
    array (
      'tablename' => 'node_data_field_event_ref_venue',
      'field' => 'field_event_ref_venue_nid_default',
      'label' => 'Venue',
      'optional' => '1',
      'is_default' => '0',
      'operator' => '1',
      'single' => '1',
    ),
    array (
      'tablename' => 'node_data_field_event_feature',
      'field' => 'field_event_feature_value_default',
      'label' => 'Feature',
      'optional' => '1',
      'is_default' => '0',
      'operator' => '1',
      'single' => '1',
    ),
  );
  $view->requires = array(node_data_field_event_date, node, node_data_field_event_ref_venue, term_node_11, node_data_field_event_feature);
  $views[$view->name] = $view;

which produces the following query

SELECT DISTINCT(node.nid), 
	node_data_field_event_date.field_event_date_value AS node_data_field_event_date_field_event_date_value, 
	node.type AS node_type, 
	node.uid AS node_uid, 
	node_data_field_event_date.field_event_date_rrule AS node_data_field_event_date_field_event_date_rrule, 
	node_data_field_event_date.delta AS node_data_field_event_date_delta, 
	node.title AS node_title, 
	node.changed AS node_changed, 
	node_data_field_event_ref_venue.field_event_ref_venue_nid AS node_data_field_event_ref_venue_field_event_ref_venue_nid 
FROM {node} node 
LEFT JOIN {content_field_event_date} node_data_field_event_date ON node.vid = node_data_field_event_date.vid 
LEFT JOIN {content_type_event} node_data_field_event_ref_venue ON node.vid = node_data_field_event_ref_venue.vid 
WHERE (node.type IN ('event')) 
	AND (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_data_field_event_date.field_event_date_value), 
		SEC_TO_TIME(-14400)), '%Y-%m-%%d\T%H:%i') >= '2009-04-28T14:16') 
GROUP BY node.nid, 
	node_data_field_event_date_field_event_date_value 
ORDER BY node_data_field_event_date_field_event_date_value ASC

And here is the view that does not work. which is almost an exact duplicate, but i have trimmed it down as much as possible to try to resolve this. I will also note that i did try removing all filters, and this started out as an imported version of the view that is actually faulty.

  $view = new stdClass();
  $view->name = 'calendar_test';
  $view->description = 'Calendar view of any date field, add a date field to the view to use it.';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = 'Calendar';
  $view->page_header = '';
  $view->page_header_format = '2';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = 'Sorry, there are no events';
  $view->page_empty_format = '1';
  $view->page_type = 'table';
  $view->url = 'admin/test';
  $view->use_pager = TRUE;
  $view->nodes_per_page = '11';
  $view->sort = array (
    array (
      'tablename' => 'node_data_field_event_date',
      'field' => 'field_event_date_value',
      'sortorder' => 'ASC',
      'options' => '',
    ),
  );
  $view->argument = array (
    array (
      'type' => 'taxid',
      'argdefault' => '2',
      'title' => '%1',
      'options' => '',
      'wildcard' => 'all',
      'wildcard_substitution' => 'All Events',
    ),
    array (
      'type' => 'content: field_event_date',
      'argdefault' => '2',
      'title' => '%2',
      'options' => 'year',
      'wildcard' => '',
      'wildcard_substitution' => '',
    ),
  );
  $view->field = array (
    array (
      'tablename' => 'node',
      'field' => 'title',
      'label' => '',
      'handler' => 'views_handler_field_nodelink',
      'options' => 'link',
    ),
    array (
      'tablename' => 'node_data_field_event_date',
      'field' => 'field_event_date_value',
      'label' => '',
      'handler' => 'date_views_field_handler_group',
      'options' => 'default',
    ),
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'distinct',
      'operator' => '=',
      'options' => '',
      'value' => array (
  0 => 'distinct',
),
    ),
    array (
      'tablename' => 'node_data_field_event_date',
      'field' => 'field_event_date_value_default',
      'operator' => '>=',
      'options' => 'now',
      'value' => '',
    ),
    array (
      'tablename' => 'node',
      'field' => 'title',
      'operator' => 'contains',
      'options' => '',
      'value' => '',
    ),
  );
  $view->exposed_filter = array (
    array (
      'tablename' => 'node',
      'field' => 'title',
      'label' => '',
      'optional' => '1',
      'is_default' => '0',
      'operator' => '1',
      'single' => '1',
    ),
  );
  $view->requires = array(node_data_field_event_date, node);
  $views[$view->name] = $view;

and its resulting query

SELECT DISTINCT(node.nid), 
	node_data_field_event_date.field_event_date_value AS node_data_field_event_date_field_event_date_value, 
	node.title AS node_title, 
	node.changed AS node_changed, 
	node_data_field_event_date.field_event_date_rrule AS node_data_field_event_date_field_event_date_rrule, 
	node_data_field_event_date.delta AS node_data_field_event_date_delta 
FROM {node} node 
LEFT JOIN {content_field_event_date} node_data_field_event_date ON node.vid = node_data_field_event_date.vid 
WHERE (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_data_field_event_date.field_event_date_value), SEC_TO_TIME(-14400)), '%Y-%m-%%d\T%H:%i') >= '2009-04-28T14:16') 
GROUP BY node.nid, 
	node_data_field_event_date_field_event_date_value 
ORDER BY node_data_field_event_date_field_event_date_value ASC

Im not sure if this helps any, but I have taken the following non-working query

SELECT DISTINCT(node.nid),
node_data_field_event_date.field_event_date_value AS node_data_field_event_date_field_event_date_value,
node.title AS node_title,
node.changed AS node_changed,
node_data_field_event_date.field_event_date_rrule AS node_data_field_event_date_field_event_date_rrule,
node_data_field_event_date.delta AS node_data_field_event_date_delta
FROM {node} node
LEFT JOIN {content_field_event_date} node_data_field_event_date ON node.vid = node_data_field_event_date.vid
WHERE (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_data_field_event_date.field_event_date_value), SEC_TO_TIME(-14400)), '%Y-%m-%%d\T%H:%i') >= '2009-04-28T14:16')
GROUP BY node.nid,
node_data_field_event_date_field_event_date_value
ORDER BY node_data_field_event_date_field_event_date_value ASC

and changed

GROUP BY node.nid,
node_data_field_event_date_field_event_date_value

to

GROUP BY node.nid

and manually executed it. and it worked like a charm. I'm just not very clear on where this is actually happening.

Subscribing.

Got the same error with the date.cck and sort by date. It is not only a random sort problem.

subscribing

Agreed with #46, that extra field in GROUP BY is killing DISTINCT. In my install I modified views_query.inc, line 415 (5.x-1.6), to be "if (($this->groupby) && ($this->no_distinct)) {", so (theoretically) if distinct is set it will only GROUP BY node.nid.

I don't yet know if that breaks anything else, but I'm sure I'll figure it out soon.

EDIT: this got duplicated.. sorry. the next one has the code used

I help in the node_gallery issue que, ran into this with a node_gallery view where distinct isn't working for one person...
http://drupal.org/node/639216#comment-2296680

subscribing

sorry, my mistake.. didn't realize this was an old issue for 5x.. I will check to see what the people are using as the 6.2.7 seems to work fine..

#46 & #48 that seems to work here too... have to check what this might break

subscribing.

Suffered the same problem with Drupal 6.14, Organic groups 6.x-2.0 and Views 6.x-2.8.

This is possibly the core distinct issue. Nobody's doing serious work on 5.x-1.x and there's other issues with similar problems, so I am closing this