Order statistic in MySQL - MySQL

[Jeffrey Rolland]

Hello, all!

I am currently keeping my student's grades in MS Excel, but I am
considering migrating to MySQL. The only problem is this: I need to
drop the *two* lowest quiz grades in computing the final grade.

Is there a way to do this in MySQL? I know how to get the *lowest* quiz
grade, but how do I compute the *two* lowset quiz grades?

Thanks in advance for any assistance you can provide.

Sincerley,
--
Jeffrey Rolland | "Beneath this mask there is more than
<wildstar200hotmail.com> | flesh. There is an idea, Mr. Creedy, and

| ideas are bulletproof. "
| - _V for Vendetta_

P.S. I didn't crosspost this because comp.databases.mysql isn't carried
on my newsserver, only Google.

[Ted]

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

[strawberry]

Yes, but you can't use a variable in a LIMIT satement - so this only
works if you use a bit of php to construct the query.

[Jeffrey Rolland]

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
<wildstar200hotmail.com> | flesh. There is an idea, Mr. Creedy, and

| ideas are bulletproof. "
| - _V for Vendetta_

[Ted]

I had assumed your database was normalized. You're right, limits the
number of records. I don't know a way to limit the number of fields.
If you don't want your database normalized, you may well have to use a
PHP/MySQL solution. If I was developing such an application, I'd have
a grades table structured so that there is one record for each
evaluation event (whether a quiz, assignment, examination &c.), in
which the limit clause would work.

Ted.

[strawberry]

I think "subtract lowest two from total" and "all but the lowest two"
are two ways of saying exactly the same thing, so that's not the issue.

Ted's right though - you should normalize your table when you pull it
in to php, but regardless of whether you normalize it or not, you will
still need to use PHP to construct the query - unless (and, as a
newcomer to mysql myself, this is entirely possible), Ted, you know
something I don't!

My longwinded answer to another question in this NG (see
comp.databases.mysql/browse_thread/thread/9893fdeb1aef4dfa)
attempts to solve a not entirely unrelated problem. Significantly, it
shows how the results of a count(*) statement can be used to assign a
variable to the LIMIT part of a subsequent query. Someone's going to
come long in a minute with a much smarter and more elegant solution
but, until they do, perhaps this will help some way to solving your
problem.

[Jerry Stuckle]

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
> <wildstar200hotmail.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.
[email]jstucklexattglobal.net[/email]
==================

[Jeffrey Rolland]

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
> > <wildstar200hotmail.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.
> [email]jstucklexattglobal.net[/email]
> ==================

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
<wildstar200hotmail.com> | flesh. There is an idea, Mr. Creedy, and
| ideas are bulletproof. "
| - _V for Vendetta_

[Jerry Stuckle]

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
>>><wildstar200hotmail.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.
>>jstucklexattglobal.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
> <wildstar200hotmail.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.
[email]jstucklexattglobal.net[/email]
==================

[Jeffrey Rolland]

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
> >>><wildstar200hotmail.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.
> >>jstucklexattglobal.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
> > <wildstar200hotmail.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.
> [email]jstucklexattglobal.net[/email]
> ==================

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
<wildstar200hotmail.com> | flesh. There is an idea, Mr. Creedy, and
| ideas are bulletproof. "
| - _V for Vendetta_

[Bill Karwin]

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.

[Jerry Stuckle]

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
>>>>><wildstar200hotmail.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.
>>>>jstucklexattglobal.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
>>><wildstar200hotmail.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.
>>jstucklexattglobal.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
> <wildstar200hotmail.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.
[email]jstucklexattglobal.net[/email]
==================

[strawberry]

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

[Ted]

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