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
<wildstar200@hotmail.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.

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

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.

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_

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.

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.

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
==================

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_

On 23 Apr 2006 10:47:46 -0700, 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).

That's handy, because if what I understand you wanting to do is correct,
you're going to run into a scope issue trying to do this as a single
query.

First, you'll need to query your student table to get a unique list of
the students. Then, use php loop driven by that result set to

SELECT * FROM tbl WHERE student_id = ?
ORDER BY grade LIMIT 2,18446744073709551615 ;

where ? is the student ID from your driver result set. The big number
that's the second parameter in the LIMIT is an arbitrarily large number.
For grades, even 1000 is probably overkill. You just want it to be at
least two more than the number of grades each student has. (Whether you
want to do a real prepare/execute or just do it via php substitution in
a concatenated string used as the query is up to you. For the number of
students in a class (or even a whole school) you'll probably not run
into a case where the overhead saved by prepare/execute matters much.)

--
They got rid of it because they judged it more trouble than it was worth.
(And considering they'd gone to great lengths to minimize its worth,
I suppose they were right.)
-- J. D. Baldwin

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
==================