31

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?

  • it would be actually nice if someone explains the reason(s) for the duplicates and the counter-measures. having 5 solutions that 'maybe' work is only semi-optimal – n3rd Jan 24 '15 at 19:41
19

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.

  • 2
    I have the sample problem, I enable use aggregation and in my filed there is not distinct select, which type I should select in Aggregation type? – Yusef Dec 4 '13 at 5:58
39

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.
  • In my question I have already mentioned that I have checked the Distinct option in the Query settings. – Ahmad Jul 30 '12 at 8:09
  • Eugene and Clive, It is work, Thanks a lot for sharing this. – user13765 Feb 15 '13 at 18:43
8

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.
5

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.

  • I want to add that there are known issues with DISTINCT not functioning properly in Views 3.3 and higher. – Patrick Feb 28 '13 at 19:22
  • 1
    Instead of doing the above you can just check the view's name and then add the group by. – Potney Switters Jul 22 '13 at 12:41
  • It works great. None of the solution here worked for me except this...coding always rock to find final solution :) – RajeevK Sep 13 '13 at 12:00
4

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.

0

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!

0

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

0

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.

0

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...

protected by Community Jul 7 '15 at 5:51

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.