How to get the column name of the result of a least function?

Question

I have an employee table that looks like this:

| id | name | q1 | q2 | q3 | q4 |
+----+------+----+----+----+----+
| 1  | John | 20 | 30 | 10 | 4  |
| 2  | Ram  | 07 | 10 | 03 | 4  |
| 3  | John | 05 | 03 | 15 | 40 |
| 4  | Sree | 12 | 05 | 20 | 25 |

I needed to get the minimum value and maximum value of questions where the id is equal to 4. In this case, I needed 5 and 25 returned. I acheived that using the following query:

SELECT id, name,
  LEAST(q1, q2, q3, q4) AS minValue,
  GREATEST(q1, q2, q3, q4) AS maxValue
FROM employee
WHERE id = 4;

But what this doesn't return is the question id. How can I adjust my query to show that q2 is the minimum and q4 is the maximum? I know I could write a big case statement, but I also feel like it could be accomplished using a join but I can't figure it out.

Note: This is for a postgresql database, but I tagged MySQL as well because I know it also supports the LEAST and GREATEST functions. If the solution is very different for both, then I will remove this note and make a separate question.

EDIT

I have an SQL Fiddle already.

Denis de Bernardy · Accepted Answer · 2014-12-19T14:25:07.117

5

You can use a case statement:

CASE
WHEN LEAST(q1, q2, q3, q4) = q1 THEN 'q1'
WHEN LEAST(q1, q2, q3, q4) = q2 THEN 'q2'
WHEN LEAST(q1, q2, q3, q4) = q3 THEN 'q3'
ELSE 'q4'
END as minQuestion

(Note: it will lose information over ties.)

If you're interested in ties, approaching it with a subquery and arrays will do the trick:

with employee as (
  select id, q1, q2, q3, q4
  from (values
    (1, 1, 1, 3, 4),
    (2, 4, 3, 1, 1)
  ) as rows (id, q1, q2, q3, q4)
)

SELECT least(q1, q2, q3, q4),
       array(
         select q
         from (values (q1, 'q1'),
                      (q2, 'q2'),
                      (q3, 'q3'),
                      (q4, 'q4')
              ) as rows (v, q)
         where v = least(q1, q2, q3, q4)
       ) as minQuestions
FROM employee e
WHERE e.id = 1;

edited Dec 19 '14 at 14:25

answered Dec 19 '14 at 14:04

Denis de Bernardy

75,850
13
131
154

2

[This worked.](http://sqlfiddle.com/#!15/8f17b/4) I know you shortened your question for simplicity but it might be helpful to mention for future users to use `ELSE` for 'q4' since you can't have all `WHEN` statements. – AdamMc331 Dec 19 '14 at 14:09
Thanks for the edit. What criteria is this using to break the tie? Does it take the first occurrence of the value? – AdamMc331 Dec 19 '14 at 14:29
Try the second query I listed (without the with part): it'll build an array that contains the name of all questions that apply in case of a tie. (Because of `v = least(q1, q2, q3, q4)`) – Denis de Bernardy Dec 19 '14 at 14:30
Okay, *that* is really cool. I have never used the array function, and that actually helps me out a lot. Here is the updated [Fiddle](http://sqlfiddle.com/#!15/b7977/3) with that included. Do you know if MySQL supports it also? – AdamMc331 Dec 19 '14 at 14:35
It doesn't. MySQL lacks a great score of Postgres features. – Denis de Bernardy Dec 19 '14 at 14:39

Kickstart · Answer 2 · 2014-12-19T14:21:02.003

I would use a CASE statement to compare the greatest value against each column until a match is found

Not perfect as it won't be fast and will also just find the first column when more than one share the same max value:-

SELECT id, 
    name,
    LEAST(q1, q2, q3, q4) AS minValue,
    CASE LEAST(q1, q2, q3, q4)
        WHEN q1 THEN 'q1'
        WHEN q2 THEN 'q2'
        WHEN q3 THEN 'q3'
        ELSE 'q4'
    END,
    GREATEST(q1, q2, q3, q4) AS maxValue,
    CASE GREATEST(q1, q2, q3, q4)
        WHEN q1 THEN 'q1'
        WHEN q2 THEN 'q2'
        WHEN q3 THEN 'q3'
        ELSE 'q4'
    END
FROM employee
WHERE id = 4;

EDIT - using a join, and I suspect this will be far worse:-

SELECT a0.id, 
    a0.name,
    LEAST(a0.q1, a0.q2, a0.q3, a0.q4) AS minValue,
    CASE 
        WHEN a1.id IS NULL THEN 'q1'
        WHEN a2.id IS NULL THEN 'q2'
        WHEN a3.id IS NULL THEN 'q3'
        ELSE 'q4'
    END,
    GREATEST(a0.q1, a0.q2, a0.q3, a0.q4) AS maxValue,
    CASE GREATEST(q1, q2, q3, q4)
        WHEN a15.id IS NULL THEN 'q1'
        WHEN a16.id IS NULL THEN 'q2'
        WHEN a17.id IS NULL THEN 'q3'
        ELSE 'q4'
    END
FROM employee a0
LEFT OUTER JOIN employee a1 ON a0.id = a1.id AND LEAST(a0.q1, a0.q2, a0.q3, a0.q4) = a1.q1 
LEFT OUTER JOIN employee a2 ON a0.id = a2.id AND LEAST(a0.q1, a0.q2, a0.q3, a0.q4) = a2.q2 
LEFT OUTER JOIN employee a3 ON a0.id = a3.id AND LEAST(a0.q1, a0.q2, a0.q3, a0.q4) = a3.q3 
LEFT OUTER JOIN employee a4 ON a0.id = a4.id AND LEAST(a0.q1, a0.q2, a0.q3, a0.q4) = a4.q4 
LEFT OUTER JOIN employee a11 ON a0.id = a11.id AND GREATEST(a0.q1, a0.q2, a0.q3, a0.q4) = a1.q11 
LEFT OUTER JOIN employee a12 ON a0.id = a12.id AND GREATEST(a0.q1, a0.q2, a0.q3, a0.q4) = a2.q12 
LEFT OUTER JOIN employee a13 ON a0.id = a13.id AND GREATEST(a0.q1, a0.q2, a0.q3, a0.q4) = a3.q13 
LEFT OUTER JOIN employee a14 ON a0.id = a14.id AND GREATEST(a0.q1, a0.q2, a0.q3, a0.q4) = a4.q14 
WHERE a0.id = 4;

Yeah, this [worked](http://sqlfiddle.com/#!15/8f17b/4) out. Do you think there's a faster solution? I *thought* there might be a join solution, but it's probably far more complicated than this and the the maintainability of a join like that might not be worth it, compared to something like this which is readable and easy to understand. — AdamMc331, Dec 19 '14 at 14:10
Not sure I can think of a clean and efficient way to do this with a join, although I am adding a horrible way of doing it! — Kickstart, Dec 19 '14 at 14:20
Yes, it just looks far worse lol. (Not your fault, though). I think a case statement is best here. And, even with that join we used a case statement. So at this point, why even bother with the join? — AdamMc331, Dec 19 '14 at 14:22
The `least` and `greatest` operators aren't expensive compared to a join: they apply to values available within the row itself -- so it's all cpu time -- and in this particular case they're only evaluated for rows that match the where clause. — Denis de Bernardy, Dec 19 '14 at 14:29

score 1 · Answer 3 · answered Dec 19 '14 at 14:22

Below will show max and min values. It will also show shared max and min values. It will fall down if max and min values happen to be the same. (eg all values are the same)

SELECT
employee.id, x.name,
CASE 
WHEN employee.q1 = x.minVal THEN 'minval' 
WHEN employee.q1 = x.maxVal THEN 'maxval'
ELSE ''
END AS q1,
CASE 
WHEN employee.q2 = x.minVal THEN 'minval' 
WHEN employee.q2 = x.maxVal THEN 'maxval' 
ELSE '' END AS q2,
CASE 
WHEN employee.q3 = x.minVal THEN 'minval' 
WHEN employee.q3 = x.maxVal THEN 'maxval' 
ELSE '' END AS q3,
CASE 
WHEN employee.q4 = x.minVal THEN 'minval' 
WHEN employee.q4 = x.maxVal THEN 'maxval' 
ELSE '' END AS q4,
x.minVal,
x.maxVal
FROM employee
INNER JOIN
(
SELECT e.id, e.name,
  LEAST(e.q1, e.q2, e.q3, e.q4) AS minVal,
  GREATEST(e.q1, e.q2, e.q3, e.q4) AS maxVal
FROM employee e
WHERE e.id = 4
 ) x
ON employee.id=x.id

This does [work](http://sqlfiddle.com/#!15/8f17b/22), my only concern that I would have to discuss with someone else is that it will return empty cells for the questions that aren't the min or max, and I don't know if we'll want that. But, +1 for a working solution. — AdamMc331, Dec 19 '14 at 14:25
You can alter the query to show the value if you like. It kinda depends on what it is used for. eg instead if having ELSE '' END you can have ELSE employee.q{x} END. You can also put the value with the minval and maxval with CONCAT — Nick Bloor, Dec 19 '14 at 14:29

Erwin Brandstetter · Answer 4 · 2021-08-10T16:46:00.150

0

Use a "simple" or "switched" CASE statement:

SELECT CASE LEAST(q1, q2, q3, q4)
          WHEN q1 THEN 'q1'
          WHEN q2 THEN 'q2'
          WHEN q3 THEN 'q3'
          ELSE 'q4'
       END as chosen_item

In case of ties, the first item in the list will be reported.

edited Aug 10 '21 at 16:46

answered Dec 19 '14 at 16:46

Erwin Brandstetter

605,456
145
1,078
1,228

How to get the column name of the result of a least function?

4 Answers4

Linked