Monday, July 2, 2012

MySql select the first row for each group

In the following table the enrollment of students in different program are shown. I want to select the last programme (ordered by date) in which each of the student is enrolled in.
id studentId programmeId startDate endDate
1 444 101 2012-01-01 2012-06-01
2 555 101 2012-01-01 2012-06-01
3 666 101 2012-01-01 2012-06-01
4 444 201 2012-06-15 2012-12-20
5 555 301 2011-07-01 2012-02-01
6 666 201 2012-06-15 2012-12-20
7 444 301 2011-07-01 2012-02-01

We have to make group of students and sort the programmes for each student by the order (descending) of their programme-end-Dates, and then select the first programme from each student-group. And the result should look like this.
studentId programme
444 201
555 101
666 201
To generate the above result the query can be written like this:
select
    R.studentId,
    (
        select
            R1.programmeId
        from
            Registration R1
        where
            R1.studentId=R.studentId
        order by
            R1.endDate DESC limit 1
    ) as "programme"
from
    Registration R
group by
    R.studentId;