How do I remove duplicates when using the random sort?

Question

I have created a view where I am displaying the the node title and a logo (a cck field). In the arguments I have passed the taxonomy term name.

In the result a node is displaying 2 or 3 times; I already checked the distinct checkbox in the Query settings but that also does not work. I have used the random sort, which seems to cause the duplicates; when I remove the random sort, it works fine.

How can I avoid the duplicates without disabling the random sort?

Answer 1

You can apply GROUP BY or DISTINCT by enabling views aggregation settings to remove duplicate records.
1. Go and edit your view
2. In Advanced » OTHER section by enabling Use aggregation: Yes
3. In FIELDS or FILTER CRITERIA section, select and apply Aggregation settings for which fields you want to group by or distinct.

Answer 2

The simple way to achieve distinct values in a view is to make the following:

1. Go and edit your view
2. In Advanced » OTHER section click on "Query setting"
3. Check "Distinct" check-box.

Answer 3

The simple way to achieve distinct values using Views is to do the following:

1. Go and edit your view
2. In Advanced » OTHER section click on "Query setting"
3. Check "Distinct" check-box.

You can apply GROUP BY or DISTINCT by enabling views aggregation settings to remove duplicate records.

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

Answer 4

I know this question has already been answered, but for some of use these solutions don't work. However, after doing some research I found a solution that does work for my needs.

I found a really useful workaround that allowed me to fix this. I have posted the code snippet to DropBucket. Basically you implement a Drupal 7 query alter hook that checks all views queries for query comments that specify a groupby clause and field. Then it adds that groupby to the SQL query.

/**
 *  Found this trick on theoleschool.com.
 *  
 *  Description: Allows the view developer to specify the query
 *  group by action in the query comments. Great way to force the
 *  removal of duplicates.
 *  
 *  Just go into your query comments and type in "groupby:" with
 *  that colon followed by the field you want to group by.
 * 
 *  Examples...
 *  groupby:node.nid
 *  groupby:file_managed_file_usage.fid
 * 
 *  Ref-Comment: http://theoleschool.com/comment/496#comment-496
 *  Ref-Article: http://theoleschool.com/blog/using-hookviewsalter-add-group-statement
 */
function mymodule_query_alter(QueryAlterableInterface $query) {
  if ($query->hasTag('views')) {
    static $count;
    $view =& $query->getMetaData('view');

    if (strstr($groupby = $view->query->options['query_comment'], 'groupby')) {
      list($action, $field) = explode(':', $groupby);

      if (strlen($field) > 0) {
        $query->groupBy($field);
      }
    }
  }
}

http://dropbucket.org/node/153

The source reference is in the comments on the page.

Answer 5

I had the same problem. Ultimately I solved it by installing the Views Distinct module.

Relationships or other joins in Views often create "duplicate" results. For example, a node with a field that has multiple values may show up in the View once per value in the multi-value field. It's frustrating, and the "DISTINCT" SQL query option in the Views UI does not actually solve the problem because the result row is technically distinct. Views Distinct aims to give a simple GUI method to remove or aggregate these "duplicate" rows.

Answer 6

Had problems with multi value date fields. The items were displayed as often as a date was entered. None of the other here mentioned solutions worked for me. But then I found a D6 solution that still works in D7 there.

hook_views_pre_render(&$view) is the magic word. In the example it's limited down to one certain display of one certain view only. Hope it helps.

function MYMODULE_views_pre_render(&$view) {
  $used_nids = array();

  if ($view->name == 'events') {

    if ($view->current_display == 'page_2') {

      foreach ($view->result as $row) {

        if (!in_array($row->nid, $used_nids)) {

          $new_view_result[] = $row;
          $used_nids[] = $row->nid;
        }
      }
      $view->result = $new_view_result;
    }
  }
}

EDIT: Unfortunately this one subtracts the number of duplicate items from the view if a limit was set. If someone maybe finds a solution for this, please comment!

Answer 7

Not to wake up an old thread, but the answer (if using Drupal 7 and an exposed search filter) is to check "remove search score" under "search:search terms".

Under "Filter Criteria"

Add "Search:Search Terms"

Check "Remove Search Score"

Click "Apply (all displays)"

Click Save

Answer 8

None of these other methods worked for me, but Views Random Seed module actually did the trick. Though, my issues were more related to using a pager. Here is a quote from that module's project page:

Adds a random order handler with seed. If a constant integer argument N is specified in the sql RAND() function, it is used as the seed value, which produces a repeatable sequence of column values. This makes it possible to have paging and not having items show up twice. It's also possible to compute a custom seed using PHP, allowing you to create pseudo randoms numbers based on variables such as the date of the day.

Answer 9

I have multiple Date fields and want to sort by the start of the FIRST date... Here is how it did work: In Views->advanced->Other: use aggregation: YES And then: SORT CRITERIA->Aggregation settings->Aggregation type: COUNT

I expected count to give me the number instead of the instance, but it made the result distinct...