Need some lights here

[Lithium]

CREATE TABLE `test` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`VALUE1` int(11) NOT NULL,

`VALUE2` int(11) NOT NULL,

`VALUE3` int(11) NOT NULL,

`VALUE4` int(11) NOT NULL,

PRIMARY KEY (`ID`)

) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

INSERT INTO test VALUES(NULL,'100','200','500','1000');

Let's say that i want to select ONLY the highest value on the fields, which can be any of them.

(SELECT * FROM table ORDER BY field DESC LIMIT 1 is not an option)

What is the best solution for such an option?

Thanks for any help!

[iSOS]

Something like this should work for ya...

mysql_query("SELECT GREATEST(`VALUE1`,`VALUE2`,`VALUE3`,`VALUE4`) AS `max` FROM `test`");

[Lithium]

Something like this should work for ya...

mysql_query("SELECT GREATEST(`VALUE1`,`VALUE2`,`VALUE3`,`VALUE4`) AS `max` FROM `test` LIMIT 1");

Heh! Definetly! Thx for the pointer Zed :)

And a note... you don't need the limit there as you're selecting only one value ;)

[iSOS]

Haha, well I know now :whistling:

Lol, cheers, It wasn't meant to be there I just copied the query you posted and added and obviously forgot to do that :L

[Lithium]

Now yet another doubt. I got the higher value, is it possible to know what field it stands for?

[a_bertrand]

I would personally say that the problem starts with the table design. If you want 4 stats (or more even maybe later on), and then check which stat is the higher, that design may work but is certainly not the smartest. Why not have like 1 row for each:

[mysql]

CREATE TABLE test (

ID int(11) NOT NULL AUTO_INCREMENT,

STAT CHAR(1) NOT NULL,

VALUE int(11) NOT NULL,

PRIMARY KEY (`ID`)

);

[/mysql]

Of course now this table is somewhat stupid as it lack the possibility to link it to something else. However with such design you can have a lot more stats and add them when you want. You may change STAT CHAR(1) to INT and have then yet more stats if needed. Question is now, how would you query the higher stat?

[mysql]

SELECT ID,STAT,VALUE ORDER BY VALUE DESC LIMIT 0,1

[/mysql]

Or Higher per stat?

[mysql]

SELECT ID,STAT,MAX(VALUE) GROUP BY ID,STAT

[/mysql]

Of course this design is slower than yours, as the table will contains lot more rows, but it will add a lot of flexibility and possibility (as you saw) to make more queries.

[Lithium]

"SELECT ID,STAT,VALUE ORDER BY VALUE DESC LIMIT 0,1" This could be the answer (with your given layout example) , yet the table layout is something i can't change unfortunatelly. Despite that, your approach made me think and i managed to get a solution, not a fancy nor practical one, but just as good.

with the examples given it ought to be something on these lines (for those who might be interested)

select ID, case

when value1 >= value2 and value1 >= value3 and value1 >= value4 then 'VALUE1'

when value2 >= value1 and value2 >= value3 and value2 >= value4 then 'VALUE2'

when value3 >= value1 and value3 >= value2 and value3 >= value4 then 'VALUE3'

else 'VALUE4' end, (SELECT GREATEST(`VALUE1`,`VALUE2`,`VALUE3`,`VALUE4`) AS `max` FROM `test`) FROM `test`

The bad from this... a big table layout needs to have a few tweaks to generate the query the fastest possible, yet the overall performance goes quite fine and acceptable :)

Thanks to Zed and a_bertrand to point me out on the right direction :thumbsup: