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.
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;