Greetings. There is a base of goods, it has the price and price_currency . The maximum value of the price MAX(price) is selected from the base. How for this value choose the corresponding price_currency in the same request?

UPD: It is undesirable to do the second request, as a very cumbersome query with joins and a large where.

  • Select price_currency from table where price = (Select MAX(price) from table) ? - Alexey Shimansky
  • The question is how to do it in one request. There is added a bunch of conditions in where when the max value is selected, so I would not want to make a second request - VK
  • Well, maybe it was worth giving a complete description of the problem with everything you have and what you need? Because in its current form, my comment solves your problem completely ......... and however that prevents you from adding to the condition .... and price = (Select MAX(price) from table) ? - Alexey Shimansky

2 answers 2

Like that:

 SELECT other_field, max_field FROM table ORDER BY max_field DESC LIMIT 1

Order the sorting by the field, which should be maximum, in the reverse order. And with the help of limit get only one record (just the required one).
If the field can be null, then you can make ORDER BY max_field DESC NULLS LAST

At the request of viewers.
What to do when the goods with the maximum price is not one?
The easiest option is to ignore everyone except the one that came first :)
But if it is impossible to ignore for some reason, then try a query with sub-queries.
Such:

 SELECT other_field, max_field FROM table t JOIN (SELECT MAX(max_field) max FROM table) m ON m.max = t.max_field

Or this:

 SELECT other_field, max_field FROM table WHERE max_field = (SELECT MAX(max_field) FROM table)

I don’t know whether they invented anything else to simplify the task.

  • And if the goods with the maximum price is not one thing? - Alexey Shimansky
  • По заявкам Ρ‚Π΅Π»Π΅Π·Ρ€ΠΈΡ‚Π΅Π»Π΅ΠΉ ...... here JOIN and PODZAPROS, which the vehicle asked not to do .... because. The citation Ρ‚Π°ΠΊ ΠΊΠ°ΠΊ сильно Π³Ρ€ΠΎΠΌΠΎΠ·Π΄ΠΊΠΈΠΉ запрос с Π΄ΠΆΠΎΠΈΠ½Π°ΠΌΠΈ ΠΈ большим where. .... and from which he refused, after I wrote my comment)) - Alexey Shimansky
  • @ Alexey Shimansky That's it! One join less, one more. What's the difference? It will index across the field and the maximum will be quickly found. Will be picky - get yourself know what. - Sergey
  • In principle, the first option should come up, prices are in old Belarusian rubles and dollars, so the difference in numbers is huge there, so the two maximum numbers are unlikely to fall with different currencies - VK
 select max(price) as price, substr(max(concat(lpad(price,10,'0'),price_currency)),11) as price_currency from table

In lpad, specify the number of add-ons greater than the price field can be. If you wish, you can make a request of any complexity and also use gorup by.