clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805509 fiddles created (40743 in the last week).

select version();
version()
5.7.34
 hidden batch(es)


CREATE TABLE item ( item_symbol CHAR(1), price DECIMAL(10,2), source_date DATE );
 hidden batch(es)


INSERT INTO item (item_symbol, price, source_date) VALUES ('A', 20.1, '2021-06-10'), ('A', 18.2, '2021-06-11'), ('A', 10.9, '2021-06-13'), ('A', 21.0, '2021-06-15'), ('B', 88.2, '2021-06-10'), ('B', 60.9, '2021-06-11'), ('B', 78.16, '2021-06-13'), ('B', 79.0, '2021-06-15'); SELECT * FROM item;
item_symbol price source_date
A 20.10 2021-06-10
A 18.20 2021-06-11
A 10.90 2021-06-13
A 21.00 2021-06-15
B 88.20 2021-06-10
B 60.90 2021-06-11
B 78.16 2021-06-13
B 79.00 2021-06-15
 hidden batch(es)


SELECT item_symbol, price, source_date, IF ( @prev <> item_symbol, @row_num := 1, @row_num := @row_num + 1 ) AS my_rank, @prev := item_symbol FROM item JOIN ( SELECT @row_num := NULL, @prev := 0 ) AS r ORDER BY item_symbol, source_date DESC, price DESC;
item_symbol price source_date my_rank @prev := item_symbol
A 21.00 2021-06-15 1 A
A 10.90 2021-06-13 2 A
A 18.20 2021-06-11 3 A
A 20.10 2021-06-10 4 A
B 79.00 2021-06-15 1 B
B 78.16 2021-06-13 2 B
B 60.90 2021-06-11 3 B
B 88.20 2021-06-10 4 B
 hidden batch(es)


SELECT item_symbol, price, source_date, my_rank -- this last one is not FROM -- required - for clarity... ( SELECT item_symbol, price, source_date, IF ( @prev <> item_symbol, @row_num := 1, @row_num := @row_num + 1 ) AS my_rank, @prev := item_symbol FROM item JOIN (SELECT @row_num := NULL, @prev := 0) AS r ORDER BY item_symbol, source_date DESC, price DESC -- in case of ties! ) AS t WHERE t.my_rank <= 2 ORDER BY item_symbol, source_date DESC; -- change this as required
item_symbol price source_date my_rank
A 21.00 2021-06-15 1
A 10.90 2021-06-13 2
B 79.00 2021-06-15 1
B 78.16 2021-06-13 2
 hidden batch(es)