Using db_select to only get the latest nodes with a certain field value

Question

I have extending a query function for a D2D migration. The nodes have a field on them for a certain type of ID. In the old D6 site the the field did not have to be unique, but in the new D7 site the field does have to be unique.

I want to use db_select so that I only get the newest nodes in the content_type_nodetype table if there are multiple nodes with the same value for that id field.

Sample table data:

content_type_nodetype
____________________________
| nid | field_someid_value |
|==========================|
| 1   | 12345              |
| 2   | 12346              |
| 3   | 12347              |
| 4   | 12345              | 

node
____________________________
| nid | created            |
|==========================|
| 1   | 1292780734         |
| 2   | 1292780735         |
| 3   | 1292780736         |
| 4   | 1292780737         |

Given that data, the query I want should only return nid 2,3, and 4 as the value for field_someid_value for nodes 1 and 4 are the same, but the created timestamp in the node table for nid 4 is later than the timestamp for 1.

Answer 1

You might try something like :

$query = db_select('node', 'n');
$query->join('content_type_nodetype', 'ct', 'ct.nid = n.nid');
$query->groupBy('field_someid_value');
$query->addExpression('MAX(n.nid)', 'nid');

The 'nid' order must follow the 'created' order so you won't need to order the results. Just taking the biggest nid for each field_someid_value should do the job.

Answer 2

It turns out there was another (custom) table in my legacy database which held another set of data for those specific IDs and the node ID's associated. All I had to do was join with that table on the specific ID and set a condition for the nid to equal the nid above.