selct max row from a table
i have a table which include fields, subjectname,title,status,marks
a subjectname can have more than one row with different marks , i have to select row with maximum marks.
for example
i have 5 rows with record.
subjectname | title| marks|status
maths | basic| 66|paid
maths | basic| 89|not paid
maths | basic | 73|paid
computer | basic | 39|paid
computer | basic | 78|paid
the result i need is
maths | basic| 89
computer|basic |78
i used
SELECT subjectName, MAX(Marks) AS Expr1
FROM OnlineTestResults
that is
maths | 89
computer|78
it ives required result but when i use
SELECT Status, TestName, MAX(Marks) AS Expr1
FROM OnlineTestResults
it give wrong result as
maths | 89|Not Paid
maths|73|paid
computer|78
can any one help me??????