Professional Web Applications Themes

Order statistic in MySQL - MySQL

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

  1. #1

    Default Order statistic in MySQL

    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.

    Jeffrey Rolland Guest

  2. #2

    Default Re: Order statistic in MySQL

    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

    Ted Guest

  3. #3

    Default Re: Order statistic in MySQL

    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.

    strawberry Guest

  4. #4

    Default Re: Order statistic in MySQL


    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_

    Jeffrey Rolland Guest

  5. #5

    Default Re: Order statistic in MySQL

    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.

    Ted Guest

  6. #6

    Default Re: Order statistic in MySQL

    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.

    strawberry Guest

  7. #7

    Default Re: Order statistic in MySQL

    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]
    ==================
    Jerry Stuckle Guest

  8. #8

    Default Re: Order statistic in MySQL

    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_

    Jeffrey Rolland Guest

  9. #9

    Default Re: Order statistic in MySQL

    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]
    ==================
    Jerry Stuckle Guest

  10. #10

    Default Re: Order statistic in MySQL

    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_

    Jeffrey Rolland Guest

  11. #11

    Default Re: 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.
    Bill Karwin Guest

  12. #12

    Default Re: Order statistic in MySQL

    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]
    ==================
    Jerry Stuckle Guest

  13. #13

    Default Re: Order statistic in MySQL

    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

    strawberry Guest

  14. #14

    Default Re: Order statistic in MySQL

    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

    Ted Guest

Similar Threads

  1. asp on site statistic
    By i-cable hk in forum ASP Components
    Replies: 0
    Last Post: December 17th, 01:50 AM
  2. MYSQL query using GROUP BY and ORDER BY?
    By Bonge Boo! in forum PHP Development
    Replies: 3
    Last Post: August 15th, 06:12 PM
  3. update statistic in dbexport file
    By Bill Hamilton in forum Informix
    Replies: 1
    Last Post: October 16th, 10:56 PM
  4. Xtras that import statistic 2D animation and accept parameters?
    By Amarin in forum Macromedia Director Lingo
    Replies: 1
    Last Post: September 22nd, 07:16 AM
  5. problem with DBI.pm and ORDER BY RAND() in mysql
    By Joe Blower in forum PERL Miscellaneous
    Replies: 1
    Last Post: July 13th, 11:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139