[Solved]Drupal 7 views: how to remove duplicates? Causing from random sort

Thanks for this post. I have tried a number of different solutions, but removing any sort criteria (mine was Sticky, descending) seems to reduce the duplicate nodes.

Here's another insight from merlinofchaos at http://drupal.org/node/1419000#comment-6543982

It's the taxonomy relationship. Once you add that relationship on a taxonomy with multiple terms per node, you're going to get duplicates pretty much no matter what you do. We do what we can to mitigate it, but ultimately we won't be able to.

My taxonomy vocabularies used in the problematic tables do allow multiple selectors, and I can't avoid this.

For different attempts to try to fix the duplicates problem, see:

Drupal 7 Views - duplicate results

Drupal 7 views: how to remove duplicates? Causing from random sort

I'm still not satisfied with removing my sort criteria, because I'd really like to put it back. The distinct query and aggregation options didn't seem to help. I even tried a "delta" setting, which I don't understand and which didn't seem to work. It's going to take more research to figure this out, I think.

When I look at the query produced when adding a sort field, the problem with just applying DISTINCT to the query is that the RAND is added as a field and is evaluated together with all other fields and as RAND by nature returns a random number each time and renders the DISTINCT pointless.

I have unfortunately had no luck with the aggregation settings and has not found a way to prevent the RAND to affect the number of results.

I have however found a way around this by implementing hook_views_query_alter and there moved the RAND from fields to only live in the ORDER BY part of the query. On this particular site I have used the module views_random_seed to implement the sort since I use pagination on this view. The code below could probably be adapted to help when using regular sort as well.

Here is the code to implement in a module of your choice to remove duplicates while using random sort:

function YOUR_MODULE_views_query_alter(&$view, &$query) {
  if($view->name == 'YOUR_VIEW_NAME') {
    $sort_field = '_random_seed';
    foreach($query->field_aliases as $aliases_key => $aliases) {
      if($sort_code = array_search($sort_field, $aliases)) {
        unset($query->field_aliases[$aliases_key][$sort_code]);
        if(empty($query->field_aliases[$aliases_key])) {
          unset($query->field_aliases[$aliases_key]);  
        }
        unset($query->fields[$sort_field]);
        $query->orderby[0]['field'] = $sort_code;
        break;
      }
    }
  }
}

This may help.

https://drupal.org/project/views_random_seed

In my case of a mystery duplicate I had a taxonomy field causing the duplicate. On "Display A" I had it grouped by a taxonomy vocabulary. In this display the duplication was desired because nodes could possess more than 1 term of that vocabulary and I wanted each group to show all the nodes that had that term. When I cloned the display and created a more simplified "Display B" that was not broken out by groups I was still getting duplicates for nodes that had more than one of the taxonomy terms assigned for the "Display A" grouping field. Despite using the techniques above I was not able to remove the duplicate from "Display B" until I removed the taxonomy field that "Display A" was using to group the nodes.

Not exactly what the OP described, but my Google search still brought me here. I have a node with 4 multivalued fields where the values in the nth position are related, i.e. a start date, end date, rate, and minimum stay requirement form a set of 4 fields.
My view is supposed to display a table of n rows with 4 columns.
For n=4, the query initially produced 4^4 rows (256) instead of 4.
I noticed that the "delta" column could in theory be used to join related rows in the field tables, but how does one go about that? Views didn't seem to expose this. I found a patch (https://www.drupal.org/node/699252#comment-7513087) that would have let me add field comparisons as filter criteria, but I am on a production system and I hate patches.
I ended up with a PHP filter. like so, and the view now returns the 4 rows that I want, i.e. it joins the rows that have the same delta:

if ($row->delta == $row->delta_1 &&
    $row->delta == $row->delta_2 &&
    $row->delta == $row->delta_3)
   return FALSE;
else   return TRUE;

A specific module exists to deal with this :
https://www.drupal.org/project/views_distinct

I want to display multiple images in my view...But it displaying empty images with other images..How to solve this?
"Hide if empty" is not working

Using aggregation:
1. Go and edit your view.
2. Advanced >> OTHER >> Use aggregation and chenge it to "Yes".
3. In FIELDS or FILTER CRITERIA section, select and apply Aggregation settings for which fields you want to group by or distinct.

Using Query Settings
1. Go and edit your view.
2. Advanced >> OTHER >> Query settings - Check Distinct checkbox and if necessary Pure Distinct too.
3. As described, this settings will hamper your site's performance and cannot guarantee it will work all the time.

Using "views_distinct" module (my personal best)
1. Download and install/enable this module.
2. Select/click any field where distinct should be set.
3. Expand 'VIEWS DISTINCT SETTINGS", under Filter/Aggregate this field select Filter Repeats or Aggregate Repeats
4. Disadvanges - this is not exportable in feature. So views_ui needs to be enabled in production which is not recommended.

I'm not pretty sure if this works in all cases, but the key for me was to eliminate the taxonomy on the FIELDS items, when I did this no more duplicates appeared...

And just to confirm a thing that most people wrote, when using Aggregation, be careful with the SORT CRITERIA, it brakes stuff.

With the module views distintict i can make this

https://www.drupal.org/project/views_distinct