title: Compare first and last SQL row with same id
body: <p>Here's my SQL Fiddle: <a href="http://sqlfiddle.com/#!2/90d45/3" rel="nofollow">http://sqlfiddle.com/#!2/90d45/3</a></p>
  
  <p>Table scheme:</p>
  
  <pre><code>CREATE TABLE domain_rankings (domain_id int, rank int, create_date datetime);
  INSERT INTO domain_rankings VALUES (1, 0, "2012-01-01");
  INSERT INTO domain_rankings VALUES (1, 2, "2012-01-02");
  INSERT INTO domain_rankings VALUES (1, 1, "2012-01-03");
  INSERT INTO domain_rankings VALUES (2, 0, "2012-01-01");
  INSERT INTO domain_rankings VALUES (2, 1, "2012-01-02");
  INSERT INTO domain_rankings VALUES (2, 2, "2012-01-03");
  INSERT INTO domain_rankings VALUES (3, 1, "2012-01-01");
  INSERT INTO domain_rankings VALUES (4, 3, "2012-01-01");
  INSERT INTO domain_rankings VALUES (4, 2, "2012-01-02");
  INSERT INTO domain_rankings VALUES (4, 1, "2012-01-03");
  </code></pre>
  
  <p>I want to get the count of the domain_id's that go up in rank comparing the first entry to the last entry (by date).</p>
  
  <p>So in this case, for the count of all ranks going up should be 2 (domain_id: 1, 2).</p>
  
  <p>domain_id 3 should not be included in the count because it has only one entry. So, probably need a subquery of HAVING COUNT(*) > 1.</p>
  
  <p>domain_id 4 also shouldn't be included in the count unless I reverse the query and want the ranks that go down.</p>
  
  <p>How should I go about solving this problem? I'm aware that I'll need subqueries, but I have no idea where to begin with it. The JSFiddle should give you an idea of where I'm stuck.</p>
  
  <p>Attempts - this returns the rows that are valid, but not the COUNT of the rows correctly:  </p>
  
  <pre><code>SELECT domain_id, COUNT(DR.domain_id)
  FROM domain_rankings DR
  WHERE
    (SELECT rank
     FROM domain_rankings
     WHERE domain_rankings.domain_id = DR.domain_id
     ORDER BY create_date ASC LIMIT 1
    ) &gt; (
      SELECT rank
      FROM domain_rankings
      WHERE domain_rankings.domain_id = DR.domain_id
      ORDER BY create_date DESC LIMIT 1
    )
  GROUP BY DR.domain_id
  HAVING count(*) &gt; 1
  </code></pre>
  
  <p>Final answer (slower of the two options):</p>
  
  <pre><code>SELECT COUNT(a.domain_id) cnt
  FROM (
    SELECT domain_id,MIN(create_date) mind, MAX(create_date) maxd
    FROM domain_rankings
    GROUP BY domain_id
    HAVING COUNT(*) &gt; 1
  ) master
  JOIN  domain_rankings a 
    ON a.domain_id = master.domain_id 
   AND a.create_date = master.mind
  JOIN  domain_rankings b
    ON b.domain_id = master.domain_id 
   AND b.create_date = master.maxd
  WHERE a.rank &lt; b.rank
  </code></pre>