sql - Compare difference between 2 rows at the same table -

I have a table with duplicate registers of students, but each line represents the curriculum and a status from that student .

I am using SQL Server 2008

Something like this:

  + -------- + ---- ---- ----- + ------------------------- + -------------- - + - --------------- + | ID | Students | DATE | Course | Position | + -------- + ------------- + ------------------------- + --------------- + ----------------- + | 21245 | Roberta Zor | 2014-01-08 00: 00: 00.000 | CINNIAS | FORMADO | | 39316 | Igor Bastos | 2008-04-07 00: 00: 00.000 | CINNIAS | Cannes | 39316 | Igor Bastos | 2014-01-08 00: 00: 00.000 | Administrator | FORMADO | | 39 9 61 | Luiz Felipe | 2014-02-12 00: 00: 00.000 | Administrator | Karzando | | 105937 | Daniel COO | 2014-02-14 00: 00: 00.000 | Administrator | Karzando | | 105937 | Daniel COO | 2014-02-10 00: 00: 00.000 | Administrator | Riseva de Vega | + -------- + ------------- + ------------------------- + --------------- + ----------------- +  

I need the most recent Is the combination of student / student status for all students.

UPDATE

I am joining someone else to get the status:

  SELECT a.ID , A.STUDENT, a.COURSE, MAX (a.DATE) as #TABLE #STUDENTS from INNER JOIN #STUDENTS B on AID = a.ID and a.COURSE = B COURSE and a.STATUS & lt; & Gt; B STUtUs Group By AID, ASTUNDNT, A Corrise CID = # TB T Inner Inner on Join #STUDENTS C #FINAL_TABLE in c.id, c. Instant, C. Corris, C. Select. .id and CSTUDNt = T. STUnent and C. Corros = T.Corse  
 class = "post-text" itemprop = "text"> This question is the most recent for each student / course combination Line will find it uses every  STUDENT  /  COURSE to find the most recent date of combination, and then uses CTE to retrieve mailing lines . The last result is the most recent row for each  STUDENT  /  COURSE  combination. Retrieve the combination with CTE_MostRecent (for each student / course): - * Student ID - * Curriculum - * Most recent entry selection date, course, maximum date as Max (date) - most recent date Select the most recent line - select the most recent line - Select the most recent line from the S * to the ST * Select the INNER JOIN CTE_MostRecent for this student / course combination. EIID SID = MIID and escoras = M.coreis and SDTE = M.maxdate  

Output ():

  ╔═ ═══════╦═════════ ════╦═════════════════════╦══════ ═════════╦═══════ ════╗ ║ ID ║ Student ║ Date ║ Course ║ Status ║ ╠═ ═══════╬═══════ ══════╬═════════════════════╬══════ ═════════╬═════ ══════╣ ║ 105937 ║ Daniel Cho ║ 2014-02-14 00:00:00 ║ Administratorialo ║ Cursando ║ ║ 39 9 61 ║ Luiz Felipe ║ 2014 -02-12 00:00:00 ║ AdministratorCoOo ║ Cursando ║ ║ 39316 ║ Igor Bastos ║ 2008-04-07 00:00:00 ║ CIÊNCIAS ║ Kansalado ║ ║ 39316 ║ Igor Bastos ║ 2014- 01-08 00:00: 00 ║ AdministratorAO ║ Fordo ║ ║ 21245 ║ Roberta Zor ║ 2014-01-08 00:00:00 ║ CIÊNCIAS ║ FORMAD ║ ╚════════╩══════ ═══════ ╩═════════════════════╩═══════════════╩════ ═══════ ╝  

Note: The output given above is taken from the actual SQL-server instance, Esc Do not ddle from ULFI

Note: DATETIME value is shown as "[Mahananam], DD YYYY14 Hm: MM: SS + 0000" > This solution assumes that you have more than one entry per student / COURSE combination per day.