Help with SQL statement: comparing multiple columns

1

Does somebody know if it’s possible with a single SQL statement to compare the values of multiple columns in one row of an MySQL database?

I want to select the lowest value from these columns.

I could do this with a loop within my PHP script, but this would mean that I need to do multiple database queries and each time compare the selected value with the previously selected value. However, preferrably I just want to do 1 query.

Cheers.

2

I’m not quite sure what you mean. But you could do
SELECT MIN(column) FROM table

That’ll return one row with the lowest value in the column.

3

ok let me explain what I mean :slight_smile:

let’s say I have a table called myTable

Inside I have several columns, user, some other cols and col1 until col10

I select one row (one user) and then I want to compare the values that are in col1 till col10 and return the lowest.

If I got that, then I’d like to delete this value and insert a new value.

Hope that clarifies it a bit. If not, let me know :stuck_out_tongue:

4

if you are using MySQL,

LEAST(col1,col2,…)

5

wow, that would be a very simple solution. thanks a lot!

one more question though: that statement would give me the actual lowest value right?

any idea how I could also retrieve the column name of the column containing this lowest value?

and does anybody know of a good resource site that offers a complete overview of possible SQL statements? 'cause I fond the tutorial at w3schools.com a bit limited.

thanks again.

6

there is no direct way…

use

CASE value
WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result …]
[ELSE result]
END

7

on second thoughts there is a function FIELD:

SELECT FIELD(‘ej’, ‘Hej’, ‘ej’, ‘Heja’, ‘hej’, ‘foo’);
-> 2

so

SELECT CONCAT(“col”, STR(FIELD(LEAST(col1,col2,…), col1,col2,…))

If your columns arent named like col1 , col2 etc you can use

ELT(N,str1,str2,str3,…)

Returns str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD():

8

Hey i gotta give myselves an A for that

9

I don’t think I understand all of that. :stunned:
I also found a chapter in the mysql.com reference manual about SQL syntax but I can’t find all the things that you use here. Are there things that I’m missing here??

Cheers.

10

Lets say you have 5 columns col1, col2, col3, col4, col5

so your query would be

SELECT
LEAST(col1, col2, col3, col4, col5) as leastval,
CONCAT(“col”, STR(FIELD(LEAST(col1, col2, col3, col4, col5), col1, col2, col3, col4, col5)) as leastfield
FROM table1

11

is this syntax for a mysql database? I can’t seem to get it to work :frowning:

12

my mistake

no need for str… the function is “convert” anyway.

use this

SELECT LEAST( col1, col2, col3, col4, col5 ) AS leastval, CONCAT( “col”, FIELD( LEAST( col1, col2, col3, col4, col5 ) , col1, col2, col3, col4, col5 ) ) AS leastfield
FROM table1