Here is my SQL query to find a row in currency_price table grouped by maximum date of inserting to table. My question is how to find the second maximum. I mean how can I change 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 id so for example i have sell and buy for each id .exm:
id sell buy
1000 500 480
1001 20 19
...
but here i want the second maximum date for each id.
I know some query to find second maximum but all does not take me to my answer.
If it is MySql then Use LIMIT 1,1; # Retrieve rows [start with rec]1-[fetch rec count]1
Use ROW_NUMBER()
Sample
SELECT * FROM
(
SELECT *,ROW_NUMBER() OVER (ORDER BY AGE DESC) RNUM FROM TEST_TABLE
) QUERY1 WHERE RNUM=2
You could manually add a group order number to your initial ordered query & then select the second from each row.
The inner query, orders as required & numbers the rows starting from 1, resetting each time the currency_id changes.
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
This could be put into a stored procedure from workbench as follows :
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 ;
And from PHP you execute "CALL SecondMaximum();"
If you wanted to be able to change tables and/or fields, then you could pass these as string variables to the procedure & create & execute a prepared statement within the stored procedure. Just do a google search for tutorials on those.