title: SELECT all records but the highest
body: <p>I have three tables.</p>
  
  <pre><code> Page                           Revision                                   Text                       
   +-----------+-------------+    +-----------+-------------+----------+     +-------------+---------+
   | page_id   | rev_id      |    | rev _id   | rev_text_id | page_id  |     | rev_text_id | text    |
   +-----------+-------------+    +-----------+-------------+----------+     +-------------+---------+
   |      1    |     2       |    |      1    |     1       |    1     |     |      1      |     A   |
   +--------------------------    +------------------------------------+     +-----------------------+
   |      2    |     5       |    |      2    |     2       |    1     |     |      2      |     B   |
   +-------------------------+    +------------------------------------+     +-----------------------+
                                  |      3    |     3       |    2     |     |      3      |     C   |
                                  +------------------------------------+     +-----------------------+
                                  |      4    |     4       |    2     |     |      4      |     D   |
                                  +------------------------------------+     +-----------------------+
                                  |      5    |     4       |    2     |               
                                  +------------------------------------+               
  </code></pre>
  
  <p>My desired output is:</p>
  
  <pre><code> +--------+-------------+
   | rev_id | rev_text_id |
   +--------+-------------+
   |   1    |      1      |
   +----------------------+
   |   3    |      3      |
   +----------------------+
   |   4    |             |
   +----------------------+
  </code></pre>
  
  <p>My goal is to find rev_id's and rev_text_id's that can be deleted from each table in a single query. As the revision table can hold equal rev_text_id's for multiple revisions, I need to exclude the rev_text_id that is highest for each page (in the example 4 for page 2).</p>
  
  <p>I began dabbling along the lines of:</p>
  
  <pre><code> SELECT
   r.rev_id,r.rev_text_id
   FROM revision r, page p, text t
   WHERE p.page_id = r.page_id
   AND t.rev_text_id = r.rev_text_id 
   AND p.rev_id != r.rev_id
   AND r.rev_text_id not in (
        SELECT max(r.rev_text_id)
        FROM revision AS r, page AS p
        WHERE r.rev_id = p.rev_id
   );
  </code></pre>
  
  <p>But that skips the record for rev_id 4 in my desired output and doesn't include the "null cases" ..</p>