selecting the minimum of seven fields

[windandwaves]

Hi Gurus

I am new to mysql. I need to select the lowest number from seven fields
where the value is greater than 0. Can someone tell me how to do this.

So far, I have

Select RF1, RU1, ItemName FROM TABLE UNION
Select RF2, RU2, ItemName FROM TABLE UNION
Select RF3, RU3, ItemName FROM TABLE UNION
Select RF4, RU4, ItemName FROM TABLE UNION
Select RF5, RU5, ItemName FROM TABLE UNION
Select RF6, RU6, ItemName FROM TABLE UNION
Select RF7, RU7, ItemName FROM TABLE;

This will give me one long list with all the rate from (RF#) and Rate UNtil
(RU#). Now how do I select the lowest one for each itemname?

TIA

- Nicolaas

[jerry gitomer]

Re: selecting the minimum of seven fields

windandwaves wrote:[color=blue]
> Hi Gurus
>
> I am new to mysql. I need to select the lowest number from seven fields
> where the value is greater than 0. Can someone tell me how to do this.
>
> So far, I have
>
> Select RF1, RU1, ItemName FROM TABLE UNION
> Select RF2, RU2, ItemName FROM TABLE UNION
> Select RF3, RU3, ItemName FROM TABLE UNION
> Select RF4, RU4, ItemName FROM TABLE UNION
> Select RF5, RU5, ItemName FROM TABLE UNION
> Select RF6, RU6, ItemName FROM TABLE UNION
> Select RF7, RU7, ItemName FROM TABLE;
>
> This will give me one long list with all the rate from (RF#) and Rate UNtil
> (RU#). Now how do I select the lowest one for each itemname?
>
> TIA
>
> - Nicolaas
>
>[/color]
Nicolaas,

The quick band-aid approach to your question can be found in
the manual. Just look up the MIN function. Also, you only need
one SQL statement to solve your problem.

The long term solution is to learn SQL. Quickest way to
start is to use an on-line tutorial.

HTH
Jerry

[windandwaves]

Re: selecting the minimum of seven fields

jerry gitomer wrote:[color=blue]
> windandwaves wrote:[color=green]
>> Hi Gurus
>>
>> I am new to mysql. I need to select the lowest number from seven
>> fields where the value is greater than 0. Can someone tell me how to
>> do this. So far, I have
>>
>> Select RF1, RU1, ItemName FROM TABLE UNION
>> Select RF2, RU2, ItemName FROM TABLE UNION
>> Select RF3, RU3, ItemName FROM TABLE UNION
>> Select RF4, RU4, ItemName FROM TABLE UNION
>> Select RF5, RU5, ItemName FROM TABLE UNION
>> Select RF6, RU6, ItemName FROM TABLE UNION
>> Select RF7, RU7, ItemName FROM TABLE;
>>
>> This will give me one long list with all the rate from (RF#) and
>> Rate UNtil (RU#). Now how do I select the lowest one for each
>> itemname? TIA
>>
>> - Nicolaas
>>
>>[/color]
> Nicolaas,
>
> The quick band-aid approach to your question can be found in
> the manual. Just look up the MIN function. Also, you only need
> one SQL statement to solve your problem.
>
> The long term solution is to learn SQL. Quickest way to
> start is to use an on-line tutorial.
>
> HTH
> Jerry[/color]

Hi Jerry

Thank you jerry.

I know SQL pretty well, I just could not find the MIN function anywhere on
the mysql website. But that does not really solve my problem as such.

You dont actually have to use the min function, you can actually use

SELECT itemname, RF1 FROM TABLE GROUP BY itemname ORDER BY RF1;


max is: ORDER BY RU1 DESC

Anyway, the hard part is having 7 fields. I actually solved the problem, but
I have about four SQL statements, I was wondering if anyone knew any
shortcuts

Thank you once more for your reply.

- Nicolaas

[Bill Karwin]

Re: selecting the minimum of seven fields

windandwaves wrote:[color=blue]
> Hi Gurus
>
> I am new to mysql. I need to select the lowest number from seven fields
> where the value is greater than 0. Can someone tell me how to do this.[/color]

MySQL includes a function LEAST() which takes a variable number of
arguments and gives back the least value.

e.g. LEAST(1, 2, 99, 5) returns 1.

See http://dev.mysql.com/doc/mysql/en/co...operators.html
[color=blue]
> This will give me one long list with all the rate from (RF#) and Rate UNtil
> (RU#). Now how do I select the lowest one for each itemname?[/color]

Do you want the lowest RF# or the lowest RU# or some other computation
of them? Your other solution you posted makes me thing that you want
the lowest RF#.

Then you have to do some tricks to make sure if one of the values is 0
it won't become the least result. One method might be to force 0 (or
NULL) values to some artificial value much higher than any legitimate
value for any RF field.

SELECT LEAST(
IF(RF1,RF1,9999 ),
IF(RF2,RF2,9999 ),
IF(RF3,RF3,9999 ),
IF(RF4,RF4,9999 ),
IF(RF5,RF5,9999 ),
IF(RF6,RF6,9999 ),
IF(RF7,RF7,9999 )), ItemName
FROM `Table`

I'm also inferring from your example that ItemName is a unique field,
i.e., there is only one row in Table per ItemName.

PS: The "seven fields" thing makes me wonder if you're going to have
more than seven fields in the future. Should this be better implemented
as a dependent table? It'd probably be easier to find the MIN() for
each ItemName that way too.

Regards,
Bill K.

[windandwaves]

Re: selecting the minimum of seven fields

Bill Karwin wrote:[color=blue]
> windandwaves wrote:[color=green]
>> Hi Gurus
>>
>> I am new to mysql. I need to select the lowest number from seven
>> fields where the value is greater than 0. Can someone tell me how to
>> do this.[/color]
>
> MySQL includes a function LEAST() which takes a variable number of
> arguments and gives back the least value.
>
> e.g. LEAST(1, 2, 99, 5) returns 1.
>
> See http://dev.mysql.com/doc/mysql/en/co...operators.html
>[color=green]
>> This will give me one long list with all the rate from (RF#) and
>> Rate UNtil (RU#). Now how do I select the lowest one for each
>> itemname?[/color]
>
> Do you want the lowest RF# or the lowest RU# or some other computation
> of them? Your other solution you posted makes me thing that you want
> the lowest RF#.
>
> Then you have to do some tricks to make sure if one of the values is 0
> it won't become the least result. One method might be to force 0 (or
> NULL) values to some artificial value much higher than any legitimate
> value for any RF field.
>
> SELECT LEAST(
> IF(RF1,RF1,9999 ),
> IF(RF2,RF2,9999 ),
> IF(RF3,RF3,9999 ),
> IF(RF4,RF4,9999 ),
> IF(RF5,RF5,9999 ),
> IF(RF6,RF6,9999 ),
> IF(RF7,RF7,9999 )), ItemName
> FROM `Table`
>
> I'm also inferring from your example that ItemName is a unique field,
> i.e., there is only one row in Table per ItemName.
>
> PS: The "seven fields" thing makes me wonder if you're going to have
> more than seven fields in the future. Should this be better
> implemented as a dependent table? It'd probably be easier to find
> the MIN() for each ItemName that way too.
>
> Regards,
> Bill K.[/color]

Hi Bill

I love your solution awesome. All your inferences are correct and yes, I
should have put it in a seperate table. but I had good reasons not to.

I think I need to learn to search the Mysql site more effectively. I know
SQL from MS Access, but it is often hard to find equivalents in MySql

Thank you once more for your help

- Nicolaas

[Michael A. Capone]

Re: selecting the minimum of seven fields

Perhaps a slight tweak on Bill's excellent solution...
SELECT LEAST(
IF(RF1,RF1,9999 ),
IF(RF2,RF2,9999 ),
IF(RF3,RF3,9999 ),
IF(RF4,RF4,9999 ),
IF(RF5,RF5,9999 ),
IF(RF6,RF6,9999 ),
IF(RF7,RF7,9999 )), ItemName
FROM `Table`
The LEAST() function will ignore nulls; eg, LEAST(5, 2, NULL, 1) still
returns 1. Therefore, it's safer to do:
SELECT LEAST(
IF(RF1,RF1,NULL ),
IF(RF2,RF2,NULL ),
IF(RF3,RF3,NULL ),
IF(RF4,RF4,NULL ),
IF(RF5,RF5,NULL ),
IF(RF6,RF6,NULL ),
IF(RF7,RF7,NULL )), ItemName
FROM `Table`

rather than relying on the upper-bound 9999, as those upper bounds have
a pesky way of getting exceeded...

Good luck!
Michael