Solved

# MYSQL sort minimum value of 3 fields

Posted on 2013-05-14
410 Views
Hi

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
0
Question by:is_numeric

LVL 48

Expert Comment

sort by (price1 + price2 + price3)
0

LVL 40

Assisted Solution

Sharath earned 125 total points
Sort by least(price1,price2,price3)
0

LVL 45

Assisted Solution

Kdo earned 125 total points
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
ORDER BY 1;
0

LVL 48

Assisted Solution

PortletPaul earned 125 total points
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).

see:
http://sqlfiddle.com/#!9/f72c5/1

where, I prefer

order by (price1+price2+price3)

but that choice is entirely yours.
0

LVL 59

Accepted Solution

Kevin Cross earned 125 total points
@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: http://sqlfiddle.com/#!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)
;
``````
0

## Featured Post

### Suggested Solutions

Mysqli Query 5 40
sql statement to select and drop 13 31
Install MySQL 5.6 and PHP on Centos Linux 6 48
how to fix this error 14 44
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…