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??????
Loading
MohamedPosted Oct 3, 2012, 7:19 PM
you ask that result contains first column and second column with max marks
so you must make group with only (subjectname,title)
Muhammad younasPosted Oct 3, 2012, 4:01 PM
Kunal VaishyaPosted Oct 3, 2012, 5:37 AM
Do Like This
SELECT Status, TestName, MAX(Marks) AS Expr1
FROM OnlineTestResults Group By Status , TestName
Santhosh Kumar JayaramanPosted Oct 3, 2012, 2:32 AM
SELECT subjectName,title, MAX(Marks) AS Expr1
FROM OnlineTestResults
group by subjectName,title