Re: SELECT Row Numbers?

[Chris]

[EMAIL PROTECTED] wrote:
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)?
 

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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]