MySQL Use WHERE statement to target particular row

问题:

I'm trying to query a table that focused on 3 different columns. I have columns nid, vid and title. I have to go through each nid value, grab the highest vid of each nid value, once I get the highest vid I then have to get the title that's in the same row of the vid. Ultimately I'll need a list of each title. I have the 1st two steps down. The only part I need help with is grabbing the correct title content because once again it has to be the title that's in the same row as the highest vid. I thought a where statement would be a good way to do it but I'm stuck. Attached is a screenshot of the table.

$queryNodeRevision = "SELECT nid, MAX(vid) as vid, title FROM node_revision WHERE title = vid GROUP BY nid";
    // line above creates variable $queryNodeRevision > 

    $results = mysqli_query($connection, $queryNodeRevision) or die("Bad Query: $results");
    // line above creates variable $results > actually queries that database and passes in variable "$queryNodeRevision"

    while ($row = mysqli_fetch_array($results)) {
      // line above creates while loop that loops through > $row = mysqli_fetch_array($results)
      // $row is variable that's set to mysqli_fetch_array (with variable $results being passed in)
      // mysqli_fetch_array > creates an associate array for each row in a table
      // $results > variable that's being passed into associative array that represents the variable that's quering "SELECT nid FROM node_revision";
      $currentNID = $row['nid'];
      // line above creates variable that represents the current 'nid' of row (aka the key)
      // $row['nid'] = gets the key # of the current 'nid'
      $currentVID = $row['vid'];
      // line above creates variable that represents the current value of the 'vid' (the number you want to compare)
      // $row['vid'] = gets the value of the current 'vid'  
      $theTitleIWant = $row['title'];
      // line above creates variable that represents the current value of the 'title'
      // $row['title'] = gets the value of the current 'title'  
      echo "<h1>" . $row['title'] . "</h1>";
      // line prints out desired 'title' into h1 tag
    } // line closes while loop

Below is the line with the where statement I'm trying to accomplish my code with.

$queryNodeRevision = "SELECT nid, MAX(vid) as vid, title FROM node_revision WHERE title = vid GROUP BY nid";[![enter image description here][1]][1]

回答1:

Consider running your aggregate query (leaving out any reference to title) that joins back to the unit level table. Below returns the title corresponding to highest vid for each nid.

SELECT n.nid, n.vid, n.title
FROM node_revision n
INNER JOIN
   (SELECT nid, MAX(vid) as max_vid 
    FROM node_revision 
    GROUP BY nid
   ) AS agg
ON agg.nid = n.nid AND agg.max_vid = n.vid