I'm trying to get all duplicate rows using db_select

Question

Before I run db_delete, I'm trying to run db_select to test that I am getting all duplicate rows, based on a specific column value.

db_select('node_revision', 'n')
    ->where('n.nid NOT IN SELECT MIN(n.nid)')
    ->groupBy('hash')
    ->execute()
    ->fetchAll();

Basically, delete (select for the test) all rows with the same hash value, except for the first one, is my goal.

I'm basing it on this page:

http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/

But for some reason I get the following error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'FROM node_revision n WHERE (n.nid NOT IN SELECT MIN(nid)) GROUP BY hash' at line 1: SELECT FROM {node_revision} n WHERE (n.nid NOT IN SELECT MIN(nid)) GROUP BY hash; Array ( ) in include() 

What am I doing wrong? Why is ; array ( ) there?

Answer 1

At the moment your query is:

SELECT 
FROM 
{node_revision} n
WHERE  (n.nid NOT IN SELECT MIN(n.nid)) 
GROUP BY hash

You need to add some fields...

db_select('node_revision', 'n')
  ->fields('n', array('vid'))
  ->where('n.nid NOT IN SELECT MIN(n.nid)')
  ->groupBy('hash')
  ->execute()
  ->fetchAll();