Order statistic in MySQL

Jeffrey Rolland wrote:
> I need to drop the *two* lowest quiz grades in computing the final grade.

> For instance, say I have 3 students and 4 quizzes. A sample data set
> for grade is
>
> studentID testID grade
> 1 1 10
> 1 2 8
> 1 3 10
> 1 4 7
> 2 1 10
> 2 2 9
> 2 3 10
> 2 4 8
> 3 1 6
> 3 2 4
> 3 3 6
> 3 4 5

I tried this, and it seems to work:

select s.studentid, s.testid, s.grade
from scores s
left join scores s2 on s.studentid = s2.studentid and s.testid <>
s2.testid and s.grade <= s2.grade
group by s.studentid, s.testid
having count(*) < 2

On the last line, the number 2 should be the total number of quizzes,
minus the 2 you want to drop. So instead of dropping the bottom two, we
only include the top N-2. In your sample, you have 4 quizzes, so the
value is 4-2, or 2.

I'm not sure if this matches the method by which you want to drop quizzes.

Regards,
Bill K.

Jeffrey Rolland wrote:
> Jerry Stuckle wrote:
>
>>Jeffrey Rolland wrote:
>>
>>>Jerry Stuckle wrote:
>>>
>>>
>>>>Jeffrey Rolland wrote:
>>>>
>>>>
>>>>>Ted wrote:
>>>>>
>>>>>
>>>>>
>>>>>>If you REALLY want the two lowest grades (or more precisely the records
>>>>>>containingthe two lowest grades), you'd use ORDER BY combined with
>>>>>>LIMIT 2 in a SELECT statement.
>>>>>>
>>>>>>I suspect what you really want is to select all grades except the two
>>>>>>lowest grades.
>>>>>>
>>>>>>If you know the number of grades, you'd use an ORDER BY, along with
>>>>>>DESC (so you're sorting in descending order), combined with a LIMIT
>>>>>>with a number two less than the number of grades. If the number of
>>>>>>grades is unknown, or can vary, then you have a little extra
>>>>>>complexity, because you'd need to use count() to get the number of
>>>>>>grades before selecting all grades except the two lowest grades.
>>>>>>
>>>>>>You can look up the exact syntax in the MySQLmanual.
>>>>>>
>>>>>>I hope this helps.
>>>>>>
>>>>>>Ted
>>>>>
>>>>>
>>>>>I want all grades except the last two.
>>>>>
>>>>>Just to be clear, I want to get all grades except for the last two
>>>>>*for* *each* *record*. I think that LIMIT limits the number of records,
>>>>>not the number of grade fields in each record. It LIMIT does limit the
>>>>>number of grade fields, that would do it.
>>>>>
>>>>>I have PHP installed on my system, so a PHP/MySQL solution would also
>>>>>work (to relpy to another poster).
>>>>>
>>>>>Thanks for your replies. I will crosspost this to
>>>>>alt.comp.databases.mysql
>>>>>
>>>>>Sincerley,
>>>>>--
>>>>>Jeffrey Rolland | "Beneath this mask there is more than
>>>>><wildstar200@hotmail.com> | flesh. There is an idea, Mr. Creedy, and
>>>>>
>>>>> | ideas are bulletproof. "
>>>>> | - _V for Vendetta_
>>>>>
>>>>
>>>>Ah, now I see your problem. Like Ted I thought your data was normalized. It
>>>>looks like you just recreated your Excel tables in MySQL, which isn't very
>>>>efficient.
>>>>
>>>>Read up (try a google search) on "database normalization". The break your one
>>>>big table into several. They could be something like:
>>>>
>>>>student
>>>> studentid name
>>>>
>>>>test
>>>> testid description
>>>>
>>>>grade
>>>> studentid testid grade
>>>>
>>>>Then you'll be able to perform the search you want plus more.
>>>>
>>>>
>>>>--
>>>>==================
>>>>Remove the "x" from my email address
>>>>Jerry Stuckle
>>>>JDS Computer Training Corp.
>>>>jstucklex@attglobal.net
>>>>==================
>>>
>>>
>>>Call me stupid (or a newbie) but I don't see how normalizing helps.
>>>
>>>For instance, say I have 3 students and 4 quizzes. A sample data set
>>>for grade is
>>>
>>>studentID testID grade
>>>1 1 10
>>>1 2 8
>>>1 3 10
>>>1 4 7
>>>2 1 10
>>>2 2 9
>>>2 3 10
>>>2 4 8
>>>3 1 6
>>>3 2 4
>>>3 3 6
>>>3 4 5
>>>
>>>Now, how would LIMIT 2 or LIMIT 6 (= 3 students * top 2 records) het me
>>>what I want? How exactly are you sorting?
>>>
>>>Sorting by studentID then by grade (descending), we have
>>>
>>>studentID testID grade
>>>1 1 10
>>>1 3 10
>>>1 2 8
>>>1 4 7
>>>2 1 10
>>>2 3 10
>>>2 2 9
>>>2 4 8
>>>3 1 6
>>>3 3 6
>>>3 4 5
>>>3 2 4
>>>
>>>so the top 6 is
>>>
>>>1 1 10
>>>1 3 10
>>>1 2 8
>>>1 4 7
>>>2 1 10
>>>2 3 10
>>>
>>>not what we want.
>>>
>>>Sorting by grade (descending) then studentID we have
>>>
>>>studentID testID grade
>>>1 1 10
>>>1 3 10
>>>2 1 10
>>>2 3 10
>>>2 2 9
>>>1 2 8
>>>2 4 8
>>>1 4 7
>>>3 1 6
>>>3 3 6
>>>3 4 5
>>>3 2 4
>>>
>>>so the top 6 is
>>>
>>>1 1 10
>>>1 3 10
>>>2 1 10
>>>2 3 10
>>>2 2 9
>>>1 2 8
>>>
>>>not what we want either.
>>>
>>>Neither has the top 6 what I want.
>>>
>>>I assume I am missing something regarding how LIMIT works.
>>>
>>>Thanks in advance for any assistance you can provide.
>>>
>>>Sincerely,
>>> --
>>>Jeffrey Rolland | "Beneath this mask there is more than
>>><wildstar200@hotmail.com> | flesh. There is an idea, Mr. Creedy, and
>>> | ideas are bulletproof. "
>>> | - _V for Vendetta_
>>>
>>
>>You select only the quiz or student you want - not the entire result set. For
>>instance - if you want the top grades for student 1, you
>>
>> SELECT testID grade
>> FROM scores
>> WHERE studentID = 1
>> ORDER BY grade DESC
>> LIMIT 2
>>
>>(Assuming 4 tests)
>>
>>This gives you
>>
>> 1 10
>> 2 10
>>
>>and drops the lower two grades.
>>
>>You could do something similar for test id.
>>
>>
>>--
>>==================
>>Remove the "x" from my email address
>>Jerry Stuckle
>>JDS Computer Training Corp.
>>jstucklex@attglobal.net
>>==================
>
>
> I see; you want me to do this FOR EACH STUDENT. This will not allow me
> to create a single nice query/report for making out grades, unless I
> use views or supplement with a loop in PHP, e.g..
>
> I was actually looking for something like LEAST(quiz1, quiz2, quiz3),
> only for 2 or more. I guess that isn't going to happen.
>
> In fact, I have a situation where some quizzes are worth 30 points,
> some are worth 40, and some are worth 20; I have a "dummy student"
> record that contains what the maximum grade for each quiz is. If I'm
> doing the report in PHP anyways, I guess that would be easy enough to
> account for in there.
>
> Thanks for all the help.
>
> Sincerely,
> --
> Jeffrey Rolland | "Beneath this mask there is more than
> <wildstar200@hotmail.com> | flesh. There is an idea, Mr. Creedy, and
> | ideas are bulletproof. "
> | - _V for Vendetta_
>

You can. You can also build a UNION, i.e. (not tested)


SELECT testID grade
FROM scores
WHERE studentID = 1
ORDER BY grade DESC
LIMIT 2
UNION
SELECT testID grade
FROM scores
WHERE studentID = 2
ORDER BY grade DESC
LIMIT 2

And so on. You could use GROUP BY, but that would only work if you have the
same number of students taking each test.

But personally I think the easiest way would be to just do it in a loop and
fetch each one separately.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

And don't forget to store the scores as percentages! I think you could
do that this way...

CREATE VIEW percentages AS SELECT
results.test_id,
student_id,
round( (score / tests.max_score) *100 ) AS percent
FROM results, tests
WHERE results.test_id = tests.test_id

Don't forget what the numbers mean! My understanding of the original
problem was that the students would be taking some number of quizes
that would be equally weighted, and that the two lowest marks would be
ignored. This is a fairly common practice in education, and I have
done it myself. However, it is not rational to do this across all
evaluation instruments since major projects and of course midterm and
final examinations are typically worth much more than any individual
quiz. I have been working on the assumption that Jerry is doing this
only for the quizes, and maybe for some exercises, and not for the more
significant or important evaluation instruments. The rationale for
ignoring one or two lowest marks for formative evaluation instruments
is to ensure that a student does not suffer much should he miss a class
or two and thus an exercise or two due to minor illness such as a flu.
But I can see no justification for doing this sort of thing for
summative evaluation instruments. IMHO, all marks on summative
evaluation instruments must count regardless of how well or how badly a
student has performed.

So, Strawberry, you're right you can create a view with percentages,
much as you describe, but given that the selection procedure should be
applied only in situations where, refering to your code,
tests.max_score is the same for all records, it isn't really necessary.

The fact that you can do something, using your favourite programming
language, does not imply that you should do it.

Cheers,

Ted