MYSQL sort minimum value of 3 fields


I have 3 fields I want to sort by in MYSQL DB.

price1, price2, price3

I want to sort my result by testing each field and sorting by the minimum value of the 3

each will contain a value and they are in no order of low to high's
Who is Participating?
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
@PortletPaul, note the sort fails if a record has all low prices (e.g., 2, 5, 6), so the sum of prices is lowest even though individually each price is higher than the least of another row. see:!9/05cb1/1

Therefore, I would combine both of the approaches. Nice work by the way Experts.
/* ... */
order by least(price1,price2,price3), (price1+price2+price3)

Open in new window

sort by (price1 + price2 + price3)
SharathConnect With a Mentor Data EngineerCommented:
Sort by least(price1,price2,price3)
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
I think that you'll need to create a column from the value of least() and sort by that.

  SELECT least (price1, price2, price3), {other columns}
  FROM xxx
PortletPaulConnect With a Mentor Commented:
it should not be necessary to add the calculation into the selection list, MySQL will permit sorting on calculations (and to be honest I do not like using column order as the sort method either - sometimes produces crazy unexpected results if code gets changed over time).


where, I prefer

order by (price1+price2+price3)

but that choice is entirely yours.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.