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