Solved

MYSQL sort minimum value of 3 fields

Posted on 2013-05-14
5
410 Views
Last Modified: 2013-05-29
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
Comment
Question by:is_numeric
5 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
sort by (price1 + price2 + price3)
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 125 total points
Comment Utility
Sort by least(price1,price2,price3)
0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 125 total points
Comment Utility
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

by:PortletPaul
PortletPaul earned 125 total points
Comment Utility
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

by:
Kevin Cross earned 125 total points
Comment Utility
@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)
;

Open in new window

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
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…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now