I have a table with subject and with marks. every subject has max and min marks, i need to update rows with their max and min marks for ever subject

I need output table like below, whenever particular maths subject comes max should be 95 and min should be 90, same for eng subject as well

CodePudding user response：

You can use window functions:

select t.*,
       max(marks) over (partition by subject) as max_marks,
       min(marks) over (partition by subject) as min_marks
from t;

CodePudding user response：

On older versions of MySQL, we can join to a subquery which finds the min/max mark values for each subject:

SELECT t1.subject, t1.Marks, t2.maxmarks, t2.minmarks
FROM yourTable t1
INNER JOIN
(
    SELECT subject, MIN(Marks) AS minmarks, MAX(Marks) AS maxmarks
    FROM yourTable
    GROUP BY subject
) t2
    ON t2.subject = t1.subject;

CodePudding user response：

select distinct subject, max(marks) max_marks, min(marks) min_marks
from table
group by subject