mysql - Select rows with the same Item name and show lowest Price Value -

mysql - Select rows with the same Item name and show lowest Price Value -

we have 2 tables laid out as:

products id | item | supplier 1 | harry potter | warner 2 | harry potter | warner 3 | game of thrones | hbo 4 | simpsons | warner 5 | simpsons | warner

and

prices id | cost 1 | 10.99 2 | 20.00 3 | 20.00 4 | 10.00 5 | 12.00

i'm trying id of lowest priced item there 2 items same name , supplier.

i can rows there duplicates as:

select products.id,products.item,products.supplier,prices.price products left bring together prices on prices.id = products.id products.id in ( select id products supplier="warner" grouping item having count(*) > 1 )

how can modify show products.id of lowest priced duplicate item name ?

i have tried order throws error me.

the result should be:

id | item | supplier | cost 1 | harry potter | warner | 10.99 4 | simpsons | warner | 10.00

thanks,

rick

use order by in subquery group by in main query

select n.id, n.item, n.supplier, n.price (select p.id, p.item, p.supplier, pr.price products p inner bring together prices pr on p.id = pr.id order cost asc) n grouping item, supplier

sample output

id | item | supplier | cost 1 | harry potter | warner | 10.99 4 | simpsons | warner | 10.00 3 | game of thrones | hbo | 20.00

to result there 2 items same name , supplier

select n.id, n.item, n.supplier, n.price (select p.id, p.item, p.supplier, pr.price products p inner bring together prices pr on p.id = pr.id order cost asc) n grouping item, supplier having count(n.id) > 1

sample output

id | item | supplier | cost 1 | harry potter | warner | 10.99 4 | simpsons | warner | 10.00

mysql