How to check if the duplicate in query SQL and use another column to choose which will for final query

Asked

Viewed 40 times

0

I have a price list and an item table, at more than a price for each item and need to pick only the one with lower value.

SELECT `nameItem`,`descItem`,`valuePrice`,`datePrice` FROM item, price WHERE item.idItem = price.fk_idItem

This darling returns them all.

mysql

  • Hi hi, put it on Sqlfiddle your example of tables and then yes, you will have better help. However, a tip, you have here the mysql MIN() function documentation. This may be an option, there are several.

1 answer

2


SELECT `nameItem`, `descItem`, MIN(`valuePrice`), `datePrice` 
FROM item, price 
WHERE item.idItem = price.fk_idItem
GROUP BY `nameItem`, `descItem`

  • Just remembering that the existence of a field in the selection list that is neither in the GROUP BY list nor in an aggregation function is not accepted in all DBMS. Mysql is allowed if ONLY_FULL_GROUP_BY is disabled.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.