Drupal 5: Querying the node table for a distinct list of updated nodes per user
This morning I was writing a query to get a distinct list of updated/added times for each node type per user. The result was going to be using in a table to show when the user last added or updated each type of node in the system. Here is an example of how to query the node table with subqueries:
<?php
$sql = "
select
n.nid, n.vid, n.created, n.changed, n.type, n.title
from {node} n
join (
select max(changed) as maxChanged, type
from {node}
group by type
) as n2 on n2.type = n.type and n.changed = n2.maxChanged
join (
select max(nid) as maxNid, type
from {node}
group by type
) as n3 on n3.type = n.type and n.nid = n3.maxNid
where 1=1
and n.uid=%d
and n.status=1
order by n.changed desc
";
$resource = db_query($sql, db_escape_string($GLOBALS['user']->uid));
$results = array();
while($row = db_fetch_array($resource)) $results[] = $row;
?>