# Help with SQL statement: comparing multiple columns

**URL:** <https://forum.kirupa.com/t/help-with-sql-statement-comparing-multiple-columns/65045>\
**Category:** Uncategorized\
**Created:** [September 22, 2004, 3:07pm UTC](https://forum.kirupa.com/t/help-with-sql-statement-comparing-multiple-columns/65045 "2004-09-22T15:07:01Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![Flashmatazz](https://yyz1.discourse-cdn.com/flex011/user_avatar/forum.kirupa.com/flashmatazz/32/140_2.png) [@Flashmatazz](https://forum.kirupa.com/u/Flashmatazz)\
**Post date:** [September 22, 2004, 3:07pm UTC](https://forum.kirupa.com/t/help-with-sql-statement-comparing-multiple-columns/65045/1 "2004-09-22T15:07:01Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [September 22, 2004, 6:59pm UTC](https://forum.kirupa.com/t/help-with-sql-statement-comparing-multiple-columns/65045/2 "2004-09-22T18:59:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [September 22, 2004, 8:34pm UTC](https://forum.kirupa.com/t/help-with-sql-statement-comparing-multiple-columns/65045/3 "2004-09-22T20:34:38Z")

</div>

ok let me explain what I mean 🙂

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 😛

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [September 23, 2004, 4:35am UTC](https://forum.kirupa.com/t/help-with-sql-statement-comparing-multiple-columns/65045/4 "2004-09-23T04:35:46Z")

</div>

if you are using MySQL,

LEAST(col1,col2,…)

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [September 23, 2004, 8:17pm UTC](https://forum.kirupa.com/t/help-with-sql-statement-comparing-multiple-columns/65045/5 "2004-09-23T20:17:01Z")

</div>

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](http://w3schools.com) a bit limited.

thanks again.

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [September 25, 2004, 4:30am UTC](https://forum.kirupa.com/t/help-with-sql-statement-comparing-multiple-columns/65045/6 "2004-09-25T04:30:24Z")

</div>

there is no direct way…

use

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

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [September 25, 2004, 4:37am UTC](https://forum.kirupa.com/t/help-with-sql-statement-comparing-multiple-columns/65045/7 "2004-09-25T04:37:53Z")

</div>

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():

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [September 25, 2004, 4:38am UTC](https://forum.kirupa.com/t/help-with-sql-statement-comparing-multiple-columns/65045/8 "2004-09-25T04:38:32Z")

</div>

Hey i gotta give myselves an A for that

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [September 25, 2004, 10:24am UTC](https://forum.kirupa.com/t/help-with-sql-statement-comparing-multiple-columns/65045/9 "2004-09-25T10:24:34Z")

</div>

I don’t think I understand all of that. :stunned:  
I also found a chapter in the [mysql.com](http://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.

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [September 27, 2004, 4:06am UTC](https://forum.kirupa.com/t/help-with-sql-statement-comparing-multiple-columns/65045/10 "2004-09-27T04:06:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [September 28, 2004, 8:27pm UTC](https://forum.kirupa.com/t/help-with-sql-statement-comparing-multiple-columns/65045/11 "2004-09-28T20:27:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [September 29, 2004, 6:10am UTC](https://forum.kirupa.com/t/help-with-sql-statement-comparing-multiple-columns/65045/12 "2004-09-29T06:10:39Z")

</div>

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