MySQL 5.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


5.6.45


create table store(id int, name varchar(50), carrier varchar(50));

create table phone(id int, name varchar(50), location int, price float);

insert into phone values
( 1  , 'iPhone XS ',1,100),
( 2  , 'Samsung S8 ',1,90), 
( 12 , 'iPhone XS MAX',1,105),            
( 25 , 'LG 5',2,70), 
( 97 , 'Samsung Galaxy S10 Plus',2,100),  
( 143 , 'iPhone XS MAX',3,110),
( 144 , 'iPhone XS',3,100),
( 162 , 'Google Pixel 2XL',4,93),         
( 163 , 'Google Pixel 3XL',4,103),         
( 194 , 'iPhone XS MAX',5,115),
( 194 , 'Samsung Galaxy S10 Plus',5,100);

insert into store values
(1,'Store 1','sprint'),
(2,'Store 2','sprint'),
(3,'Store 3','sprint'),
(4,'Store 4','sprint'),
(5,'Store 5','sprint');

 SELECT q.id phone_id, q.name phone_name, q.store_id
   FROM
   (
    SELECT p.id, p.name, p.location as store_id, p.price,
           @rn := IF(@store = p.location, @rn + 1, 1) AS phone_rank,
           @store := p.location as sto
      FROM phone p
      JOIN (SELECT @store := 1, @rn := 0) as q_iter
     WHERE EXISTS ( SELECT 1 FROM store s WHERE s.id = p.location and s.carrier ='sprint' ) 
     ORDER BY p.location, p.price DESC
    ) q
  WHERE phone_rank = 1  
  ORDER BY q.store_id

 


phone_id
phone_name
store_id


12
iPhone XS MAX
1

97
Samsung Galaxy S10 Plus
2

143
iPhone XS MAX
3

163
Google Pixel 3XL
4

194
iPhone XS MAX
5

phone_id	phone_name	store_id
12	iPhone XS MAX	1
97	Samsung Galaxy S10 Plus	2
143	iPhone XS MAX	3
163	Google Pixel 3XL	4
194	iPhone XS MAX	5