function - MySQL: Query not always returning lowest most recent price across multiple stores -

the below sql returning lowest recent price each product if there price information single store, if there price information more 1 store recent lowest price isn't returned!?

wanted outcome

return lowest price product recent price information across stores if there 6 stores display lowest price recent price record each of 6 stores.

if recent price same across store price records , updated date same records can ordered store select price product in question.

{product id} value passed in depending product being displayed.

sql

select `vsp`.`prod_id` , `vsp`.`price` , `vsp`.`store` , `vsp`.`updated` `price` `vsp` not exists (     select *      `price` `vsp2`     `vsp2`.`prod_id` = `vsp`.`prod_id`     , `vsp`.`prod_id` = {product id}     , (`vsp2`.`updated` > `vsp`.`updated` or (`vsp2`.`updated` = `vsp`.`updated` , `vsp2`.`price` < `vsp`.`price`)) ) , `vsp`.`prod_id` = {product id} 

price

prod_id     | price     | store | updated -------------------------------------------------------- product 1   | 1.99      | 1     | 2016-01-20 00:00:00 product 2   | 1.49      | 1     | 2016-01-20 00:10:00 product 2   | 1.19      | 2     | 2016-01-20 00:00:00 product 3   | 12.49     | 1     | 2016-01-20 00:00:00 product 3   | 12.49     | 2     | 2016-01-20 00:00:00 product 4   | 9.89      | 1     | 2016-01-20 00:00:00 product 5   | 10.00     | 1     | 2016-01-20 00:10:00 product 5   | 9.99      | 2     | 2016-01-20 00:00:00 product 5   | 10.49     | 3     | 2016-01-20 00:00:00 

expected output

product 1   | 1.99      | 1     | 2016-01-20 00:00:00 product 2   | 1.49      | 1     | 2016-01-20 00:10:00 product 3   | 12.49     | 1     | 2016-01-20 00:00:00 product 4   | 9.89      | 1     | 2016-01-20 00:00:00 product 5   | 9.99      | 2     | 2016-01-20 00:00:00 

current output

product 1   | 1.99      | 1     | 2016-01-20 00:00:00 product 2   | 1.49      | 1     | 2016-01-20 00:10:00 product 3   | 12.49     | 1     | 2016-01-20 00:00:00 product 4   | 9.89      | 1     | 2016-01-20 00:00:00 product 5   | 10.00     | 1     | 2016-01-20 00:10:00 

update #1

the select looks working great , returning 1 price when add prodid constraint onto query, issue if add select statement function gives below error though see 1 value returned when run select on it's own.

result consisted of more 1 row 

sql function

create function fn_get_set_lowest_price (`prodid` varchar(20)) returns double begin     declare `latestprice` double(7,2) default 0;      select p3.price     `latestprice`     price p3     inner join     (         select p1.prod_id, min(p1.price) minprice         price p1         inner join         (             select prod_id, min(updated) minupdated             price             group prod_id         ) p2         on p1.prod_id = p2.prod_id , p1.updated = p2.minupdated         group p1.prod_id     ) t     on p3.prod_id = t.prod_id , p3.price = t.minprice     p3.prod_id = prodid;      return `latestprice`; end// delimiter ; 

i gravitate towards using series of inner joins result set, rather complicated subquery in where clause, hard read.

select p3.prod_id, p3.price, p3.store, p3.updated price p3 inner join (     select p1.prod_id, min(p1.price) minprice     price p1     inner join     (         select prod_id, min(updated) minupdated         price         group prod_id     ) p2     on p1.prod_id = p2.prod_id , p1.updated = p2.minupdated     group p1.prod_id ) t on p3.prod_id = t.prod_id , p3.price = t.minprice 

follow link below running demo:

sqlfiddle

update:

if want use query function, have create stored procedure. reason function must return 1 value. if contrive query should return single value, mysql still complain.

try along following:

create procedure fn_get_set_lowest_price(prodid varchar(20)) begin     select p3.price     price p3     inner join     (         select p1.prod_id, min(p1.price) minprice         price p1         inner join         (             select prod_id, min(updated) minupdated             price             group prod_id         ) p2         on p1.prod_id = p2.prod_id , p1.updated = p2.minupdated         group p1.prod_id     ) t     on p3.prod_id = t.prod_id , p3.price = t.minprice     p3.prod_id = prodid; end