CODEWITHSUNDEEP
phpmysql
Dustin Blake
Dustin Blake

Reputation: 579

MySQL/PHP Determine lowest values in table with multiple value columns

Ok I have a table with columns like

id, name, c_price1, p_price2, c_price3, p_price4

I need to figure out a mysql query which will only search the c_price1 & c_price2 columns and return the lowest 5 values within the table that do not equal 0.00

Your help is much appreciated.

Upvotes: 1

Views: 1495

Answers (2)

Harmen
Harmen

Reputation: 669

untested:

select min(price1, price2) as our_min from some_table having our_min > 0 order by our_min limit 5;

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332581

Use the LEAST function:

  SELECT LEAST(t.c_price1, t.c_price2) AS lowest
    FROM YOUR_TABLE t
   WHERE LEAST(t.c_price1, t.c_price2) != 0.00
ORDER BY lowest
   LIMIT 5

Can't use a column alias in the WHERE clause, the earliest MySQL supports is the GROUP BY but that's not the case for all databases.

Upvotes: 1

Related Questions