Need distinct title instead of nid in the result

You probably don't really understand DISTINCT.

Distinct afaik really just compares the full result. So if there are other things different it will not work, as your node nid at the moment.

Really distinct wouldn't help here, because the nid changes from node to node.

In general if you want to groupby the node title you could use groupby, but this is only implemented in views3.
In general you could try to get rid of the node: nid by removing the link flag on the node title field.

From what you said the same title appears for different types, why not filter by content type?

This isn't about understanding or not understanding DISTINCT. As far as I can see, a bug with Views (2, at any rate) seems to be that before you add any fields at all it puts the nid on the list. When you tick "Distinct?" it then does DISTINCT(nid) without letting you tell it "no, I want DISTINCT(title)". Even DISTINCT(*) would be fine, if it weren't retrieving the nid.

+1 for this problem - I've no idea how to solve it if (as the original poster suggests) hook_views_query_alter isn't working. Right now I want to do a set of archive links for a view e.g:

* August 2011
* July 2011
* June 2011

... etc. But you can't get this out of a node-based view, because it returns an entry e.g. "August 2011" for every node in August; in turn because DISTINCT always applies to the nid, whether you want to retrieve it or not.

As an example, I've created a view with a single field of node created (no filters or any other configuration.) I then switched on Distinct. Here's the SQL from Views' preview:

SELECT DISTINCT(node.nid) AS nid, node.created AS node_created FROM node node GROUP BY nid

As you can see, I've got node.nid, which I never asked for. This results in:

Post date: 22 Nov 2010
Post date: 22 Nov 2010
Post date: 22 Nov 2010
Post date: 22 Nov 2010
Post date: 5 Jan 2011
Post date: 11 Jan 2011
Post date: 14 Jan 2011
Post date: 14 Jan 2011
Post date: 14 Jan 2011
Post date: 14 Jan 2011

because of the hidden, unwanted node.nid .

The following hook_views_query_alter works for me on this specific view:

function MYMODULE_views_query_alter(&$view, &$query) {
  unset($query->fields['nid']);
  $query->count_field['field'] = 'created';
  $query->count_field['alias'] = 'node_created';
}

Obviously you don't want this to happen for every view. You could trigger it off $view->name, but even nicer would be to trigger it off GUI configuration. There's no brilliant solution to that: views tags are basically just views tag - you can only have one tag per view, really - but maybe something like:

function MYMODULE_views_query_alter(&$view, &$query) {

  // Does our view have the "distinct" tag? If so, report on possible
  // distinctness to admin users only
  if ($view->tag == "distinct" && user_access("administer site configuration")) {
    // Possible fields from query
    $possible_values = array();
    foreach($query->fields as $field) {
      $possible_values[] = "distinct:" . $field['table'] . ':' . $field['field'];
    }
    // Message
    drupal_set_message(t(
      "Distinctness not configured for %view . Possible options: !opt",
      array("%view" => $view->name, "!opt" => join(", ", $possible_values))
    ));
    return;
  }

  // Extract configuration from comma-delimited string
  preg_match('/^distinct:([^:]+):([^:]+)$/', $view->tag, $matches);
  if (count($matches) != 3) { return; }

  // Loop over the fields until we find the right one and extract the field
  // configuration including $field['alias']
  foreach($query->fields as $field) {
    if ($field['table'] == $matches[1] && $field['field'] == $matches[2]) {
      // Get rid of unwanted nid field
      unset($query->fields['nid']);
      // Override count field
      $query->count_field['table'] = $field['table'];
      $query->count_field['field'] = $field['field'];
      $query->count_field['alias'] = $field['alias'];
      return;
    }
  }
}

If you tag a view as "distinct" and then visit it as an admin, this function tells you the possible fields you can configure to be distinct. Then reconfigure them with the relevant tag e.g. "distinct:node:created", and it should work. Here's my output on the same view:

Post date: 2 Nov 2002
Post date: 6 Nov 2002
Post date: 1 Dec 2002
Post date: 15 Dec 2002
Post date: 27 Feb 2003
Post date: 28 Feb 2003
Post date: 19 Mar 2003
Post date: 8 May 2003
Post date: 14 May 2003
Post date: 22 May 2003

At least on views2 there is probably no easy way to get rid of the nid, without things like hook_views_query_alter.

+1 on dropping nid, and in my case some other fields. why include other fields than the one's selected under fields ?

select distinct nid, something, type from tables makes no sense if you only selected 'something'

(in my case distinct date in format F Y like #4)

we understand why distinct is not returning what we want, we don't understand how to drop the accessory fields...

Dear fellow Drupal enthusiasts,

This issue is now lasting for a very long time in the issue queue and was unfortunately never solved. As Drupal is a open source project, everyone is helping on a voluntary basis. That this was not solved is nothing personal and means no harm. But perhaps no one had time to deal with this issue, maybe it is too complex, or the problem was not described comprehensibly.

But this issue is not the only one. There are thousands of issues on Drupal.org that have never been worked on or could not be processed. This means that we are building a wave that is unmanageable and it is a problem for the Drupal project as a whole. Please help us keep the issue queue smaller and more manageable.

Please read again, "Making an issue report" and see if you can improve the issue. Test the problem with the current Core and modules. Maybe the problem doesn't exist anymore, is a duplicate or has even been solved within this issue but never closed.

Help can also be found for it on IRC and in the user groups.

In order to close this issue, I have set this issue to "Closed (won't fix)".

If there is new information, please re-open the issue by changing the status to active.

--
This issue was edited with the help of Issue Helper