By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE student (Student_Name, Maths, Science, Social) AS
SELECT 'A', 20, 30, 10 FROM DUAL UNION ALL
SELECT 'B', 10, 20, 30 FROM DUAL UNION ALL
SELECT 'C', 10, 10, 10 FROM DUAL;
3 rows affected
SELECT student_name,
CASE
WHEN maths = LEAST(maths, science, social) THEN 'Maths'
WHEN science = LEAST(maths, science, social) THEN 'Science'
WHEN social = LEAST(maths, science, social) THEN 'Social'
END AS minimum,
CASE
WHEN maths = GREATEST(maths, science, social) THEN 'Maths'
WHEN science = GREATEST(maths, science, social) THEN 'Science'
WHEN social = GREATEST(maths, science, social) THEN 'Social'
END AS maximum
FROM student;
| STUDENT_NAME | MINIMUM | MAXIMUM |
|---|---|---|
| A | Social | Science |
| B | Maths | Social |
| C | Maths | Maths |
SELECT student_name,
LTRIM(
CASE WHEN maths = LEAST(maths, science, social) THEN 'Maths' END
|| CASE WHEN science = LEAST(maths, science, social) THEN ', Science' END
|| CASE WHEN social = LEAST(maths, science, social) THEN ', Social' END,
', '
) AS minimum,
LTRIM(
CASE WHEN maths = GREATEST(maths, science, social) THEN 'Maths' END
|| CASE WHEN science = GREATEST(maths, science, social) THEN ', Science' END
|| CASE WHEN social = GREATEST(maths, science, social) THEN ', Social' END,
', '
) AS maximum
FROM student;
| STUDENT_NAME | MINIMUM | MAXIMUM |
|---|---|---|
| A | Social | Science |
| B | Maths | Social |
| C | Maths, Science, Social | Maths, Science, Social |