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'.

SELECT r1.* FROM remark r1 LEFT JOIN remark r2 ON (r1.serial_no = r2.serial_no AND r1.remark_no < r2.remark_no) WHERE r2.remark_no IS NULL; -- this return me the last record — Kim Stacks, Mar 16 '13 at 18:53
See if this question helps: http://stackoverflow.com/q/1895110/1275871. If so, you can sort the table in descending RemarkNo then keep the second row for each SerialNo. Easy in DBs that support ROW_NUMBER window function. — BellevueBob, Mar 16 '13 at 19:05

score 5 · Accepted Answer · answered Mar 16 '13 at 19:28

5

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.)

answered Mar 16 '13 at 19:28

Thank you, Mark. I have edited your SQLFiddle a little bit to make it more readable for myself. http://sqlfiddle.com/#!2/d2cd5/4 – Kim Stacks Mar 17 '13 at 01:02

score 0 · Answer 2 · answered Mar 16 '13 at 19:20

Here is some sql pseudo-code to get you started:

select
  penultimate.*
from data as penultimate
left join (
  /* query on data to return last row frome each group */
) as ultimate
  on /* sql to join 2nd last record on each group to last group */
where not ultimate.SerialNo is null

score 0 · Answer 3 · answered Mar 16 '13 at 19:21

Completely ineffective solution, but works ...

SELECT
  SerialNo,
  RemarkNo,
  (SELECT Desp
   FROM Remarks
   WHERE SerialNo = r3.SerialNo AND RemarkNo = r3.RemarkNo) Desp
FROM (
  SELECT
      r.SerialNo,
      (SELECT r2.RemarkNo
       FROM Remarks r2
       WHERE r.SerialNo = r2.SerialNo
       ORDER BY r2.RemarkNo DESC
       LIMIT 1, 1) RemarkNo
  FROM (SELECT DISTINCT SerialNo FROM Remarks) r) r3

Working example: http://sqlfiddle.com/#!2/a1f89/22

Select the SECOND LAST record in each group

3 Answers3

Linked