CODEWITHSUNDEEP
sqlsql-server
Bruno Pinto
Bruno Pinto

Reputation: 2013

Compare difference between 2 rows at the same table

I have a table with duplicate registers of students, but each row represent a course and a status from that student.

I'm using SQL SERVER 2008

Something like that:

+--------+-------------+-------------------------+---------------+-----------------+
|   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 |
+--------+-------------+-------------------------+---------------+-----------------+

I need the most recent STATUS from the combination STUDENT/COURSE for all Students.

UPDATE

To get the STATUS I'm using another join:

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 <> 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

Upvotes: 1

Views: 119

Answers (3)

Jon Senchyna
Jon Senchyna

Reputation: 8037

This query will find the most recent row for each Student/Course combination. It uses a Common Table Expression to find the most recent date for each STUDENT/COURSE combination, and then uses that CTE to get the matching rows. The end result is the most recent row for each STUDENT/COURSE combination.

WITH
CTE_MostRecent AS (
  -- For each student/course combination, retrieve:
  --  * student ID
  --  * course
  --  * date of most recent entry
  SELECT ID,
         COURSE,
         MAX(DATE) AS MaxDate -- Most recent date
  FROM StudentCourses
  GROUP BY ID,
           COURSE
)
SELECT S.*
FROM   StudentCourses AS S
-- Only select the the most recent row
-- for this STUDENT/COURSE combination
INNER JOIN CTE_MostRecent AS M
    ON  S.ID = M.ID
    AND S.COURSE = M.COURSE
    AND S.DATE = M.MaxDate

Output (SQLFiddle): 

╔════════╦═════════════╦═════════════════════╦═══════════════╦═══════════╗
║   ID   ║   STUDENT   ║        DATE         ║    COURSE     ║  STATUS   ║
╠════════╬═════════════╬═════════════════════╬═══════════════╬═══════════╣
║ 105937 ║ DANIEL CHO  ║ 2014-02-14 00:00:00 ║ ADMINISTRAÇÃO ║ CURSANDO  ║
║  39961 ║ LUIZ FELIPE ║ 2014-02-12 00:00:00 ║ ADMINISTRAÇÃO ║ CURSANDO  ║
║  39316 ║ IGOR BASTOS ║ 2008-04-07 00:00:00 ║ CIÊNCIAS      ║ CANCELADO ║
║  39316 ║ IGOR BASTOS ║ 2014-01-08 00:00:00 ║ ADMINISTRAÇÃO ║ FORMADO   ║
║  21245 ║ ROBERTA ZOR ║ 2014-01-08 00:00:00 ║ CIÊNCIAS      ║ FORMAD    ║
╚════════╩═════════════╩═════════════════════╩═══════════════╩═══════════╝

Note: The output above is taken from an actual SQL-Server instance, not from SQLFiddle. SQLFiddle displays DATETIME values as "[MonthName], DD YYYY 14 HH:MM:SS+0000"

Note: This solution assumes that you have at most one entry per STUDENT/COURSE combination per day.

Upvotes: 2

KumarHarsh
KumarHarsh

Reputation: 5094

    select * from 
    (select *,ROW_NUMBER()over(partition by COURSE,STATUS order by dates)rn 
     from @student)t4 where rn=1

Upvotes: 1

Ganz
Ganz

Reputation: 169

The query is 

SELECT a.id, a.student, a.course, MAX(a.date) as hight_date
FROM table  a
INNER JOIN table b on a.course = b.course
WHERE a.status != b.status
GROUP BY  a.id,a.student, a.course

Upvotes: 0

Related Questions