Select the SECOND LAST record in each group

Question

There is a table Remark that contains data as shown below:

       SerialNo | RemarkNo  | Desp
=============================================
             10 |         1 | rainy
             10 |         2 | sunny
             11 |         1 | sunny
             11 |         2 | rainy
             11 |         3 | cloudy
             11 |         4 | sunny
             12 |         1 | rainy

What query will return the following result:

             10 |         1 | rainy
             11 |         3 | cloudy
             12 |      null | null

That is, the second last record in each group should be returned?

Assuming all the RemarkNo for a SerialNo are continuous. The larger the remark number, the later the remark was made. Hence, the second last RemarkNo for SerialNo 10 is 1 with Desp 'rainy'.

Answer 1

Try:

select s.SerialNo, r.RemarkNo, r.Desp
from (select SerialNo, max(RemarkNo) maxRemark from Remark group by SerialNo) s
left join Remark r on s.SerialNo = r.SerialNo and s.maxRemark-1 = r.RemarkNo

(SQLFiddle here.)