>>>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_
>>>
Bookmarks