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]
