tags:

views:

43

answers:

2

Look at this MySQL table:

+------------+-----------+
| date_key   | stude_key |
+------------+-----------+
| 2005-09-01 | COM       | 
| 2005-09-02 | COM       | 
| 2005-09-06 | COM       | 
| 2005-09-07 | COM       | 
| 2005-09-08 | COM       | 
| 2005-09-09 | COM       | 
| 2005-09-12 | COM       | 
| 2005-09-01 | YUM       | 
| 2005-09-02 | YUM       | 
| 2005-09-06 | YUM       | 
| 2005-09-07 | YUM       | 
| 2005-09-08 | YUM       | 
| 2005-09-09 | YUM       | 
| 2005-09-12 | YUM       | 
| 2005-09-01 | DSA       | 
| 2005-09-02 | DSA       | 
| 2005-09-06 | DSA       | 
| 2005-09-07 | DSA       | 
| 2005-09-01 | JRA       | 
+------------+-----------+

It contains the days when a class took place, and student attendance. I want to know the students that gave up (stop going to classes) and when. A student cannot miss a class and return.

For instance, the course took place between 01 and 12 of September 2005. COM and YUM attended all the classes but DSA gave up on 2009-09-07 and JRA only got 1 class.

I'm trying to get a table with:

+------------+-----------+
| gave_up    | student   |
+------------+-----------+
| 2005-09-07 | DSA       | 
| 2005-09-01 | JRA       | 
+------------+-----------+

Any ideas on how to efficiently do that?

Thanks

+1  A: 

If you're just after the last entry for each student:

SELECT MAX(date_key) AS gave_up, student
FROM [table_name]
GROUP BY student
Simon Righarts
+4  A: 
SELECT MAX(date_key) AS gave_up, stude_key AS student
 FROM thetable
 GROUP BY stude_key
 HAVING MAX(date_key) != '2005-09-12'
Alex Martelli