How to find a max number in a row of a table.?

Question

if i am having a table

id name maths physics english total

s1 aaa 80 75 42 197

s2 bbb 90 56 48 194

s3 ccc 100 72 68 240

s4 ddd 45 60 60 165

in this table i want "bbb" max mark ? what will be the sql for this?

Answer 1

Well first off, the table isn't designed properly. Need to normalize.

You are comparing different fields along with a total - which I assume you do not want pull out the value.

but in it's current design, you would need to choose a class

so

Select max(maths) from table

where name='bbb'

Tables should be

Student (SID, Name)

Math (MathID, SID, Grade)

Physics (PhysicsID, SID, Grade)

English (EnglishID, SID, Grade)