How to search for the lowest value between columns?

Asked

Viewed 1,017 times

-5

I have a table with the product fields, price 1, price 2, price 3.

How do I show the product at the lowest price?

inserir a descrição da imagem aqui

database ms-access
  • 2

    Give an example of the data contained in the tables and the result you expect with this data

  • MIN() wouldn’t solve? See this related question too: Show lowest value in Mysql

  • SELECT min(price) FROM table

1 answer

1

SELECT produto, LEAST(preco1, preco2, preco3) FROM precos WHERE codigo=?

The operator least(), will select the smallest argument of a given query.

With two or more arguments, returns the smallest argument (minimum value)... If the arguments comprise a mixture of numbers and strings, they are compared as numbers.

To update the melhorpreco, just run the following query:

UPDATE precos SET melhorpreco = LEAST(preco1, preco2, preco3) WHERE codigo=?

Notes

Where were placed the ? you must enter the registration code you want to make the change.

You can see this SQL Fiddle that demonstrates what you want too.

  • Thanks. Access does not accept this function

  • 1

    You could have informed your DBMS in the question. Did you see the problem of not elaborating a question completely? Recommend to avoid later problems: [tour]

  • Yes, I forgot. Sorry.

Browser other questions tagged database ms-access

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