mysql select last nth number of row for each group -

mysql select last nth number of row for each group -

i have table attendence_session contains date of taken attendance each group(sem). want lastly 3rd date of attendance of every group. means want know on dates lastly 3rd attendance taken.

table name attendence_session

id sem date 1 3 2014-06-02 2 3 2014-06-03 3 3 2014-06-04 4 3 2014-06-05 5 3 2014-06-06 6 14 2014-05-01 7 14 2014-05-26 8 14 2014-05-27 9 14 2014-05-28 10 14 2014-05-29 11 14 2014-05-30 12 14 2014-05-31 13 14 2014-06-02 14 7 2014-06-01 15 7 2014-06-02 16 7 2014-06-03 17 7 2014-06-04 18 10 2014-06-02 19 10 2014-06-03 20 10 2014-06-04 21 10 2014-06-05 22 3 2014-06-07 23 3 2014-06-09 24 3 2014-06-10 25 3 2014-06-11 26 3 2014-06-12 27 3 2014-06-13 28 3 2014-06-14 29 3 2014-06-16

i want result should this

sem date 3 2014-06-13 7 2014-06-02 10 2014-06-03 14 2014-05-30

note attendance not taken on daily basis. please help

you can in where clause using standard sql:

select a.* attendance_session ats 3 = (select count(*) attendance_session ats2 ats.sem = ats2.sem , ats2.date <= ats.date );

if want date, can utilize group_concat()/substring_index() trick:

select a.sem, substring_index(substring_index(group_concat(date), ',', 3), ',', -1) thirddate attendance_session ats grouping a.sem;

mysql