need help with SQL query

[54uydf]

I want to make a query that would return data about students who failed in a specific course, the report is looking like this-
studId, studName, last grade, year last course was taken, num of fails in this course.
so I'm having problems writing the correct SQL query to get the year on which the student took the course last time, and the grade he got on that year.

table data looks like this-
studId, courseId, yearTaken, grade, pass/fail
1111, 1, 2008, 20, failed
1111, 1, 2009, 30, failed
1111, 1, 2010, 40, failed

so the result of the query should be like this-
1111, 1, 2010, 40, 3(count numOfFails)

but I get always other year and grade like-
1111, 1, 2009, 30, 3

I made a view to order the table by studId, year DESC but I still get the same results as with the unordered table. since I can't use the ORDER BY before I do the GROUP BY to get the numOfFails I don't know what to try anymore..

here's my SQL for now-

SELECT * , count( viewstudent_course_orderedby.courseId ) AS numOfFails
FROM viewstudent_course_orderedby
WHERE viewstudent_course_orderedby.courseId = '1111'
GROUP BY viewstudent_course_orderedby.studentId

[debasisdas]

Why you need YEAR if try to display all the failures ?

Does the same course id repeats across all the years ?

[debasisdas]

Why you need YEAR if try to display all the failures ?

Does the same course id repeats across all the years ?

[54uydf]

I want the report to show when was the last time the student took the course AND what grade he got on his last try AND how many times he took the course in total

the courseId stays the same

[debasisdas]

try this

select studId, courseId, max(yearTaken), grade, count(courseid) as no_of_times
from table
group by studId, courseId

[54uydf]

IT WORKS!!!!!!!!!!!!!!
thank you thank you thank you!!!