how to get last and secondlast record.? and how to use it in as sub query.?

Question

+----+-------------------------+--------+------------+-------+--------+--------------+------------+
| id |        user_email       | cat_id | sub_cat_id | score | out_of | score_in_per | date       |
+----+-------------------------+--------+------------+-------+--------+--------------+------------+
| 13 | inststudent@yopmail.com | 9      | 11         | 40    | 40     | 100          | 22-04-2017 |
+----+-------------------------+--------+------------+-------+--------+--------------+------------+
| 14 | inststudent@yopmail.com | 9      | 11         | 37    | 40     | 92.5         | 22-04-2017 |
+----+-------------------------+--------+------------+-------+--------+--------------+------------+
| 26 | inststudent@yopmail.com | 9      | 11         | 36    | 40     | 88           | 23-04-2017 |
+----+-------------------------+--------+------------+-------+--------+--------------+------------+
| 27 | inststudent@yopmail.com | 9      | 11         | 35    | 40     | 80           | 23-04-2017 |
+----+-------------------------+--------+------------+-------+--------+--------------+------------+

From above table i want to get the record like this can anyone help me/

+-----------+-----------------+---------------+-------------------------+--------+------------+-------+--------+--------------+------+
| lastScore | secondLastScore | maxPortaScore |        user_email       | cat_id | sub_cat_id | score | out_of | score_in_per | date |
+-----------+-----------------+---------------+-------------------------+--------+------------+-------+--------+--------------+------+
| 80        | 88              | 100           | inststudent@yopmail.com | 9      | 11         | -     | -      | -            | -    |
+-----------+-----------------+---------------+-------------------------+--------+------------+-------+--------+--------------+------+

This is my query:

SELECT 
scor.id,
(SELECT score_in_per 
FROM tbl_student_skill_score 
WHERE cat_id = scor.cat_id and sub_cat_id = scor.sub_cat_id and scor.user_email='inststudent@yopmail.com'
ORDER BY scor.date DESC,scor.id DESC LIMIT 1)
as lastScore,

(SELECT score_in_per 
FROM tbl_student_skill_score
WHERE cat_id=scor.cat_id and sub_cat_id=scor.sub_cat_id and scor.user_email='inststudent@yopmail.com' 
ORDER BY scor.date DESC,scor.id DESC LIMIT 1,1)
as secondLastScore,

(SELECT max(cast(score_in_per as decimal(5,2))) 
FROM tbl_student_skill_score 
WHERE cat_id = scor.cat_id and sub_cat_id=scor.sub_cat_id) 
as maxPortaScore,

scor.user_email,scor.cat_id,
scor.sub_cat_id,scor.score, scor.out_of,
scor.score_in_per,scor.date
FROM 
tbl_student_skill_score scor 
LEFT JOIN 
tbl_skilltest_subcategory subc 
ON 
scor .sub_cat_id = subc.scat_id 
LEFT JOIN 
tbl_skilltest_category catg 
ON subc.cat_id = catg.id 
where 
scor.user_email = 'inststudent@yopmail.com'
GROUP BY 
sub_cat_id 
ORDER by 
scor.id DESC,scor.date DESC

Form the above Query lastScore and secondLastScore are not working

lastScore means the last stored record in my case from 1st table it is id:27 record. so the result should be80

similarly

secondLastScore means the second last stored record in my case from 1st table it is id:26 record. so the result should be 88

maxPortaScore means the max score of that particular category across the table it is not relates to the particular student in my case it is 100 i have used the same user for example but actually it can be any user score.[THIS IS WORKING ABSOLUTLY FINE]

Answer 1

This query seems to be a bit of complicate, here I do choose to use more less joins to do this:

select
    (
        select t1.score_in_per
        from tbl_student_skill_score t1
        where t1.user_email = t.user_email
        and t1.cat_id = t.cat_id
        and t1.sub_cat_id = t.sub_cat_id
        and t1.id = max(t.id) -- this will get the max(id) record in each group
        limit 1
    ) lastScore,
    (
        select t1.score_in_per
        from tbl_student_skill_score t1
        where t1.user_email = t.user_email
        and t1.cat_id = t.cat_id
        and t1.sub_cat_id = t.sub_cat_id
        and t1.id < max(t.id) -- this will get all the record which id less than max(id), then use `order by t1.id desc limit 1`, of course the second last record will be retrieved.
        order by t1.id desc
        limit 1
    ) secondLastScore,
    max(score_in_per) as maxPortaScore,
    user_email, `cat_id`, `sub_cat_id`
from tbl_student_skill_score t
group by `user_email`, `cat_id`, `sub_cat_id`

some other joins, you should add them yourself.
And if there is only one record in each group, the secondLastScore will be NULL.

Answer 2

You can do that in three steps: first of all you get the last id and the maximum score for each user

select  user_email, max(id) max_id, max(score_in_per) max_score
from    tbl_student_skill_score
group by user_email

Then you get the second last id by joining the table with the above query

select  t2.max_id, max(id) as second_max, t2.max_score, user_email
from    tbl_student_skill_score t1
right join (
            select  user_email, max(id) max_id, max(score_in_per) max_score
            from    tbl_student_skill_score
            group by user_email
        ) t2
on      t1.user_email = t2.user_email
where   t1.id < t2.max_id
group by user_email

Finally you can join all this with the original table to get categories information and the score associated with last and second last id

select  t2.score_in_per as lastScore,
        t3.score_in_per as secondLastScore,
        t1.max_score as maxPortaScore,
        t2.user_email,
        t2.cat_id,
        t2.sub_cat_id
from    (
            select  t2.max_id, max(id) as second_max, t2.max_score, user_email
            from    tbl_student_skill_score t1
            right join (
                        select  user_email, max(id) max_id, max(score_in_per) max_score
                        from    tbl_student_skill_score
                        group by user_email
                    ) t2
            on      t1.user_email = t2.user_email
            where   t1.id < t2.max_id
            group by user_email
        ) t1
join    tbl_student_skill_score t2
on      t1.user_email = t2.user_email and
        t1.max_id = t2.id
left join
        tbl_student_skill_score t3
on      t1.user_email = t3.user_email and
        t1.second_max = t3.id