title: sqlite2: Joining max values per column from another table (subquery reference)?
body: <p>I'm using the following database:</p>
  
  <pre><code>CREATE TABLE datas (d_id INTEGER PRIMARY KEY, name_id numeric, countdata numeric);
  INSERT INTO datas VALUES(1,1,20);  //(NULL,1,20);
  INSERT INTO datas VALUES(2,1,47);  //(NULL,1,47);
  INSERT INTO datas VALUES(3,2,36);  //(NULL,2,36);
  INSERT INTO datas VALUES(4,2,58);  //(NULL,2,58);
  INSERT INTO datas VALUES(5,2,87);  //(NULL,2,87);
  CREATE TABLE names (n_id INTEGER PRIMARY KEY, name text);
  INSERT INTO names VALUES(1,'nameA'); //(NULL,'nameA');
  INSERT INTO names VALUES(2,'nameB'); //(NULL,'nameB');
  </code></pre>
  
  <p>What I would like to do, is to select all values (rows) of <code>names</code> - to which all columns of <code>datas</code> will be appended, for the row where <code>datas</code>.<code>countdata</code> is maximum for <code>n_id</code> (and of course, where <code>name_id = n_id</code>). </p>
  
  <p>I can somewhat get there with the following query: </p>
  
  <pre><code>sqlite&gt; .header ON
  
  sqlite&gt; SELECT * FROM names AS n1 
      LEFT OUTER JOIN (
          SELECT d_id, name_id, countdata FROM datas AS d1 
          WHERE d1.countdata IN (
              SELECT MAX(countdata) FROM datas 
              WHERE name_id=1 
              ) 
          ) AS p1 ON n_id=name_id;
  
  n1.n_id|n1.name|p1.d_id|p1.name_id|p1.countdata
  1|nameA|2|1|47
  2|nameB|||
  </code></pre>
  
  <p>... however - obviously - it only works for a single row (the one explicitly set by <code>name_id=1</code>).</p>
  
  <p>The problem is, the SQL query fails whenever I try to somehow reference the "current" <code>n_id</code>:</p>
  
  <pre><code>sqlite&gt; SELECT * FROM names AS n1 
      LEFT OUTER JOIN (
          SELECT d_id, name_id, countdata FROM datas AS d1 
          WHERE d1.countdata IN (
              SELECT MAX(countdata) FROM datas 
              WHERE name_id=n1.n_id 
              ) 
          ) AS p1 ON n_id=name_id;
  
  SQL error: no such column: n1.n_id
  </code></pre>
  
  <p>Is there any way of achieving what I want in Sqlite2??</p>
  
  <p>Thanks in advance, </p>
  
  <p>Cheers!</p>