Select the SECOND LAST record in each group
dml, greatest-n-per-group, mysql, mysql-workbench, sqlyog
Solution
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.)
Problem
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'.