Jeff,
Thaks for the reply, let me sum up. Normal order if by CaseID, However,
we have a need to order it by FileNumber (Keeps all the Assignments for One
person together). Assignments for one person may come in over the course of
Months so I can't order it by CaseID. The reason I want to do a LIMIT when
I search for a FileNumber is to have the ability to move forward and
backwards in the Recordset. So, What I would like to do is have the ability
to move back/forward a record at a time. At the moment, this means
retrieving all of the CaseID's. The other headache about this, is since I
am retrieving all of the CaseID's it does not use the index. So my best
solution is to retrieve 10-100 CaseID's before the position I jump into, and
10-100 after. So the solution on pulling 10 after with a LIMIT Clause is
great. But How do I pull the list of 10 before?
Or if someone has a better solution I would love to here it.
Roger
-----Original Message-----
From: Jeff Kilbride [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 01, 2003 3:18 AM
To: Roger Davis; [EMAIL PROTECTED]
Subject: Re: Help with Proper SQL to Limit number of rows
Hi Roger,
> Quick rundown.
>
> Table:
>
> Assignments
> CaseID auto_increment
> FileNumber char(18)
> FirstName char(20)
> LastName char(20)
> and on and on and on
> Indexes on
> CaseID (Primary)
> FileNumber
> Name (LastName, FirstName)
>
> What I would like to do is something along the lines of
>
> SELECT CaseID FROM Assignments WHERE FileNumber = '111-2332-12232-299'
ORDER
> BY FileNumber;
My first question is: why are you ordering by FileNumber when you are
specifying a FileNumber in the WHERE clause? All FileNumbers for all records
selected will be the same, won't they? (is this supposed to be ORDER BY
CaseID?)
> But I want to pull up the previous 10 and next 10 Cases (Loading the list
of
> CaseID's is starting to slow down the program). I looked at LIMIT but
don't
> know how to use that to do what I want.
Using the LIMIT clause is actually pretty easy. To select the first 10
records:
SELECT CaseID FROM Assignments WHERE FileNumber = '111-2332-12232-299' ORDER
BY FileNumber LIMIT 0, 10
The first number in the LIMIT clause is the offset and the second number is
the number of records to return. So, to select the second group of 10
records:
SELECT CaseID FROM Assignments WHERE FileNumber = '111-2332-12232-299' ORDER
BY FileNumber LIMIT 10, 10
This just says "start at record 10 and give me the next 10 records". 3rd
group:
SELECT CaseID FROM Assignments WHERE FileNumber = '111-2332-12232-299' ORDER
BY FileNumber LIMIT 20, 10
"start at record 20 and give me the next 10 records", etc... So, in your
code you can use something like a page number to figure out the offset you
need -- hence the reason this is usually called "paging". You should use
(page number - 1) * (number of records on each page) -- in your case, page 1
would give offset 0, page 2 offset 10, page 3 offset 20, etc... If this is a
web page, the page number can be sent to your script via a POST/GET
variable. Hope this helps.
Thanks,
--jeff
> Failing that, I would like to be able to do something along the lines of
>
> SELECT NEXT CaseID FROM Assignments .....
>
> After the initial pull.
>
> Any help/suggestions would be greatly appreciated.
>
> Thanx
> Roger
>
> SQL/MYSQL
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
