Member Avatar for garyjuano

i had a database with 4 column and each column has a value, below are the sample.

----------------------
col1-col2-col3-col4 --
1------4-----7---------
5------3----------10---
6------------5----12---


my question is how can i get the lowest value in each row?
here's an output that i wanted to get:

1
3
5

and here's my syntax that i used

"select least(col1,col2,col3,col4) as price from test"

but the output is null, i guess due to the column that has a null value..

thnx n advance I'd greatly appreciated your help.

Edited by garyjuano because: n/a
  • 2 Contributors
  • 3 Replies
  • 141 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for pritaeas

Documented as problematic, see SO. I suggest you use an IF on all columns to return a very large value when NULL .

SELECT
  LEAST(
    IFNULL(col1, CAST(-1 AS UNSIGNED)),
    IFNULL(col2, CAST(-1 AS UNSIGNED)),
    IFNULL(col3, CAST(-1 AS UNSIGNED)),
    IFNULL(col4, CAST(-1 AS UNSIGNED))
  ) AS `price`
from `test`
Edited by pritaeas because: n/a
Member Avatar for garyjuano

Documented as problematic, see SO. I suggest you use an IF on all columns to return a very large value when NULL .

SELECT
  LEAST(
    IFNULL(col1, CAST(-1 AS UNSIGNED)),
    IFNULL(col2, CAST(-1 AS UNSIGNED)),
    IFNULL(col3, CAST(-1 AS UNSIGNED)),
    IFNULL(col4, CAST(-1 AS UNSIGNED))
  ) AS `price`
from `test`

your codes output is null/empty again, but anywy thnx for your reply, i just view your link to solve my problem..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.