0

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: n/a

2
Contributors
3
Replies
5
Views
5 Years
Discussion Span
Last Post by pritaeas
0

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: n/a

0

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..

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.