Chris <[EMAIL PROTECTED]> wrote on 05/10/2005 12:20:57 PM: > Harald Fuchs wrote: > > >In article <[EMAIL PROTECTED]>, > >[EMAIL PROTECTED] writes: > > > > > > > >>hi, > >>your need is: > >>select * from temp LIMIT 3,4; > >>-- 3 because you have to take the fourth and 4 because dist=3+1 > >> > >> > > > >This does not make sense. A SELECT without an ORDER BY returns the > >rows in some undefined order. If you use "LIMIT 3,4" without ORDER BY, you > >get four rows out of an unordered set, so it's virtually identical to > >"LIMIT 4". As long as the original poster doesn't say what ordering > >he wants, there's no way to tell him a solution. > > > > > > > > > Actually I do have an ORDER BY column in my real query, I trimmed it out
> for the sake of brevity. This column is a unique integer, but there are > gaps of between the numbers, so I can't do a purely numerical solution. > It must be based on what order the rows were returned from the query as > far as I can tell. > > So do you need just the 4 or 5 records _after_ a target ID or are you looking to "bracket" the target id (show me the record where ID=4 and the two records before and after it)? If you are only worried about ID+few following records you can say SELECT <field list> FROM table_name WHERE ID>=<target value> ORDER BY ID LIMIT 5 That would give you your ID record and the 5 before immediately after. For your target ID + 2 records on either side you could say ( SELECT ID, <field list> FROM table_name WHERE ID>=<target value> ORDER BY ID LIMIT 3 ) UNION ( SELECT ID, <field list> FROM table_name WHERE ID < <target value> ORDER BY ID desc LIMIT 2 ) ORDER BY ID; Doing it this way, you don't need to know the "position" of a row because everything is based off of the row's id. It may not be as fast as some other ways but since your ID value is unique (I hope it's your primary key) then it should be indexed and these queries will be just about as fast as it gets. As everyone has stressed to the point of frustration, the concept of "position" only has meaning in an ordered set of results and only for the moment in time that the results were created. In the few tenths of a second it would take you to query a table, find a record, notice it's position, then requery a table based on that position, a few dozen records could have been added or deleted making your position-based query inaccurate. Trying to prevent that by locking the table would just make everything else come to a grinding halt until you had found the records you were looking for. Make your queries based on the PK value of the table you are dealing with. That way records can come and go as they please and your "positional arithmetic" will never be wrong. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
