How can I find the second maximum of my query

Question

Here is my SQL query to find a row in a table currency_price, grouped by the maximum insertion date in the table. My question is how to find the second maximum . I mean, how can I modify this query to find the second maximum row in each group:

select currency_id,buy,sell 
from (select * from currency_price  order by `currency_id`, cu_date desc,buy,sell) x 
group by `currency_id`

with this query, I found a row for each identifier, so for example, I sell and buy for each id.exm:

id    sell buy
1000  500  480
1001  20   19
...

but here I want a second maximum date for each id.

I know some request to find the second maximum, but everything does not bring me to my answer.

Answer 1

MySql, LIMIT 1,1; # [start with rec] 1- [fetch rec count] 1

http://dev.mysql.com/doc/refman/5.7/en/select.html

Answer 2

ROW_NUMBER()

SELECT * FROM 
(
SELECT *,ROW_NUMBER() OVER (ORDER BY AGE DESC) RNUM  FROM TEST_TABLE
) QUERY1 WHERE RNUM=2

Answer 3

, .

, , 1, currency_id.

set @num := 0, @ci := -1;

select currency_id,buy,sell 
from
  (select *,
    @num := if(@ci = currency_id, @num + 1, 1) as gp_number,
    @ci := currency_id as dummy 
  from currency_price
  order by `currency_id`, cu_date desc,buy,sell) x
where gp_number=2

workbench :

DELIMITER $$
CREATE PROCEDURE SecondMaximum()
BEGIN
set @num := 0, @ci := -1;

select currency_id,buy,sell 
from
  (select *,
    @num := if(@ci = currency_id, @num + 1, 1) as gp_number,
    @ci := currency_id as dummy 
  from currency_price
  order by `currency_id`, cu_date desc,buy,sell) x
where gp_number=2;
END$$

DELIMITER ;

PHP "CALL SecondMaximum();"

/ , . Google .