SGreen@stripped wrote:
>Chris <listschris@stripped> wrote on 05/10/2005 12:20:57 PM:
>
>
>
>>Harald Fuchs wrote:
>>
>>
>>
>>>In article <1115681350.427ff24647102@stripped>,
>>>mfatene@stripped 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)?
>
>
In some instances I will need the X records *after* , and in others I'll
need the X records *before*, but never both. The target row will always
be in the result set, and either be first or last.
>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.
>
>
>
I have a Primary Key (duh), but it's not the number I'm ordering by. I
misspoke in my previous email. The order column is supposed to be
unique, but due to my need to change the column numbers around at times
I can't define it as unique.
Despite that this query above definitely appears to be the sort of thing
I need. Only difference is that , since I don't need both sides, I'll
only need to run either the first of the two queries, or the last (while
keeping the UNION ORDER BY to reorder them)
>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.
>
>
Yeah, I really don't want to do any table locking, I'm doing my best
just to get it all in one query.
>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.
>
>
That's whjat I'm trying to do. At this moment I'm wishing 4.1.x had
Stored procedures, they would make my life a bit easier I think.
>Shawn Green
>Database Administrator
>Unimin Corporation - Spruce Pine
>
>
Thanks, I appreciate it.
Chris
