SELECT statement help

[intrivious]

Here is my setup:
==============
uploads
--------
upload_id (PK)
assignment_id (FK)
student_id (FK)
grade
==============

A student can have multiple uploads per assignment.

Having all that, I need a SELECT statement to retrieve the max number of upload records per student per assignment.

For example, jonny uploads 3 times, sally 2 times, and jack 1 time.
So, there would be 6 total records per some assignment_id.

What SELECT statement could return 3, since that is the max number uploads per student, per this assignment?

Thanks for the help.

[intrivious]

I found a solution using two queries, and I think two queries may be necesary on this one.


$numUploads = pg_query($dbconn,"SELECT student_id FROM students WHERE section_id=".$section_id);
$maxColumns = 0;
while ($row = pg_fetch_array($numUploads))
{
$numUploads2 = pg_fetch_array(pg_query($dbconn,"SELECT count(upload_id) FROM uploads WHERE student_id=".$row[0]));
if ($maxColumns < $numUploads2[0])
$maxColumns = $numUploads2[0];
}


Code is a little ugly and short-handed, sorry.
Thanks for the views, or unwritten responses ;)

[chazzy]

select count(*),student_id,assignment_id FROM uploads GROUP BY STUDENT_ID, ASSIGNMENT_ID

[intrivious]

That works great! Definately saves a few lines ;) ty

[sridhar_423]

wont this suffice if only the number of records uploaded per assignment are required..
select sum(count(*)) FROM uploads GROUP BY ASSIGNMENT_ID