List:General Discussion« Previous MessageNext Message »
From:Chris Date:May 10 2005 6:16pm
Subject:Re: SELECT Row Numbers?
View as plain text  
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
Thread
SELECT Row Numbers?Chris10 May
  • Re: SELECT Row Numbers?mfatene10 May
    • Re: SELECT Row Numbers?Chris10 May
  • Re: SELECT Row Numbers?Harald Fuchs10 May
    • Re: SELECT Row Numbers?Marco Neves10 May
    • Re: SELECT Row Numbers?Chris10 May
      • Re: SELECT Row Numbers?SGreen10 May
        • Re: SELECT Row Numbers?Chris10 May
  • Re: SELECT Row Numbers?Rhino10 May
    • Re: SELECT Row Numbers?mfatene10 May
  • Re: SELECT Row Numbers?Harald Fuchs10 May
    • Re: SELECT Row Numbers?Alec.Cawley10 May
    • Re: SELECT Row Numbers?Harald Fuchs10 May
      • Re: SELECT Row Numbers?Eric Bergen10 May