Second Last records

Question

I am trying to get the second last records use mysql. I did some research, some sample has fix gap between numbers or date. But my situation is that the contract_id is not always +1 from the previous one. Anyone ideas? Thank you so much.

merchant_id  	 contract_id     start_date
10	              501            2016-05-01
10	              506            2016-06-01
13	              456            2015-12-01
13	              462            2016-01-01
14	              620            2016-06-01
14	              642            2016-07-01
14	              656            2016-07-05
	
	
merchant_id  	Second_last_contract_id
10	            501
13	            456
14	            642

contract_id != previous contract_id + X. (The X is not fixed)

'start_date' tell us the contracts creating order.

Answer 1

Here's another option, filtering the results of GROUP_CONCAT() using SUBSTRING_INDEX():

SELECT   merchant_id,
         SUBSTRING_INDEX(SUBSTRING_INDEX(
             GROUP_CONCAT(contract_id ORDER BY start_date DESC),
         ',', 2), ',', -1) AS Second_last_contract_id
FROM     the_table
GROUP BY merchant_id

See it on sqlfiddle.

Answer 2

Here's one option using user-defined variables to establish a row number per group of merchants and then filtering on the 2nd in each group ordered by contracts:

select * 
from (
  select *, 
      @rn:=if(@prevMerchantId=merchantid,
              @rn+1,
              if(@prevMerchantId:=merchantid, 1, 1)
              ) as rn
  from yourtable cross join (select @rn:=0, @prevMerchantId:=null) t
  order by merchantId, contractid desc
  ) t
where rn = 2

• SQL Fiddle Demo