How can I find the column which is used by LEAST() in mysql?

Question

I get the least price from five columns by using this function in mysql:

field1: 9
field2: 10
field3: 6
field4: 15
field5: 55

select least(field1,field2,field3,field4,field5) as smallest from myTable;

So my result will be "6" (taken from field3). But I want to know the name of the column, which has the least value. So I expect "field3" as result.

I tried it "backwards", so I could try every column if the "6" is the correct value. It works, but this seems to be a ridiculous plan, because in my real application I have overall 24 columns to check. Any nice ideas?

As a note: It's NOT possible to modify the tables structure... I've to work with one single table containing all prices in 24 different columns.

Consider to normalize that table structure when selecting with `UNION` (research pivot / convert columns to records on this website) then it can be simply `SELECT MIN(..) FROM (SELECT )` assuming you don't need groups otherwise `GROUP BY` is also needed.. — Raymond Nijland, Oct 05 '19 at 10:47
If you cant fix schema.. then you will need to use long conditional statements like: `CASE WHEN col1 = LEAST(col1,col2,...col24) THEN 'col1' WHEN col2 = LEAST(col1, col2,...col24) THEN 'col2' .... END` — Madhur Bhaiya, Oct 05 '19 at 10:47
If you still can't get it working.. See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Raymond Nijland, Oct 05 '19 at 10:48
@RaymondNijland I don't quite understand your plan... I must not change the table structure... so I need to use "pure" SQL to solve this problem. It's a part of a bigger job which will be used in a stored procedure. — Marco, Oct 05 '19 at 10:51

score 2 · Accepted Answer · answered Oct 05 '19 at 11:02

You can use a solution like the following:

SELECT LEAST(field1, field2, field3, field4, field5) AS smallest,
  ELT(FIELD(LEAST(field1, field2, field3, field4, field5), field1, field2, field3, field4, field5), 'field1', 'field2', 'field3', 'field4', 'field5') 
FROM myTable;

demo on dbfiddle.uk

score 0 · Answer 2 · answered Oct 05 '19 at 11:13

It's just a simple and readable CASE statement:

case least(field1, field2, field3, field4, field5)
    when field1 then 'field1'
    when field2 then 'field2'
    when field3 then 'field3'
    when field4 then 'field4'
    when field5 then 'field5'
end

How can I find the column which is used by LEAST() in mysql?

2 Answers2