CODEWITHSUNDEEP
mysql
S.J.
S.J.

Reputation: 19

mysql select last nth number of row for each group

I have a table attendence_session which contains date of taken attendance for each group(sem). I want to get last 3rd date of attendance of every group. means i want know that on which dates last 3rd attendance was 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 be like this

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

Note attendance is not taken on daily basis. please help

Upvotes: 0

Views: 93

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269453

You can do this in the where clause using standard SQL:

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

If you just want the date, you can also use the group_concat()/substring_index() trick:

select a.sem,
       substring_index(substring_index(group_concat(date), ',', 3), ',', -1) as thirddate
from attendance_session ats
group by a.sem;

Upvotes: 2

Related Questions