title: Compare difference between 2 rows at the same table
body: <p>I have a table with duplicate registers of students, but each row represent a course and a status from that student.</p>
  
  <p>I'm using SQL SERVER 2008</p>
  
  <p>Something like that:</p>
  
  <pre><code>+--------+-------------+-------------------------+---------------+-----------------+
  |   ID   |   STUDENT   |          DATE           |    COURSE     |     STATUS      |
  +--------+-------------+-------------------------+---------------+-----------------+
  |  21245 | ROBERTA ZOR | 2014-01-08 00:00:00.000 | CIÊNCIAS      | FORMADO         |
  |  39316 | IGOR BASTOS | 2008-04-07 00:00:00.000 | CIÊNCIAS      | CANCELADO       |
  |  39316 | IGOR BASTOS | 2014-01-08 00:00:00.000 | ADMINISTRAÇÃO | FORMADO         |
  |  39961 | LUIZ FELIPE | 2014-02-12 00:00:00.000 | ADMINISTRAÇÃO | CURSANDO        |
  | 105937 | DANIEL CHO  | 2014-02-14 00:00:00.000 | ADMINISTRAÇÃO | CURSANDO        |
  | 105937 | DANIEL CHO  | 2014-02-10 00:00:00.000 | ADMINISTRAÇÃO | RESERVA DE VAGA |
  +--------+-------------+-------------------------+---------------+-----------------+
  </code></pre>
  
  <p>I need the most recent STATUS from the combination STUDENT/COURSE for all Students.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>To get the STATUS I'm using another join:</p>
  
  <pre><code>SELECT a.ID, a.STUDENT, a.COURSE, MAX(a.DATE) as DATE
  
  into #TABLE
  FROM #STUDENTS a
  INNER JOIN #STUDENTS b
  on a.ID = a.ID
  and a.COURSE = b.COURSE
  and a.STATUS &lt;&gt; b.STATUS
  GROUP BY a.ID,a.STUDENT, a.COURSE
  
  
  select c.ID, c.STUDENT, c.COURSE, c.STATUS
  into #FINAL_TABLE
  from #TABLE t
  inner join #STUDENTS C
  on C.ID = T.ID and C.STUDENT = T.STUDENT and C.COURSE = T.COURSE
  </code></pre>