By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tbl(
quiz_1_marks decimal(5,2),
quiz_2_marks decimal(5,2),
quiz_3_marks decimal(5,2),
quiz_4_marks decimal(5,2)
);
insert into tbl (quiz_1_marks, quiz_2_marks, quiz_3_marks, quiz_4_marks)
values
(86.5 ,90.3 ,69.9, 43.2 ),
(36.27 ,54.9 ,28.8, 69.65)
;
select * from tbl;
| quiz_1_marks | quiz_2_marks | quiz_3_marks | quiz_4_marks |
|---|---|---|---|
| 86.50 | 90.30 | 69.90 | 43.20 |
| 36.27 | 54.90 | 28.80 | 69.65 |
select max1, max2, max3, max4
from tbl
cross join lateral (
select
max(case rn when 1 then qm end) max1,
max(case rn when 2 then qm end) max2,
max(case rn when 3 then qm end) max3,
max(case rn when 4 then qm end) max4
from (
select q.m qm, row_number() over(order by q.m desc) rn
from (
select quiz_1_marks union all
select quiz_2_marks union all
select quiz_3_marks union all
select quiz_4_marks
) q(m)
) t
)t;
| max1 | max2 | max3 | max4 |
|---|---|---|---|
| 90.30 | 86.50 | 69.90 | 43.20 |
| 69.65 | 54.90 | 36.27 | 28.80 |