Find neighboring rows

Let's say I have a single column table like this:

Name
----------
Sam
Daniel
Artur
Andrew
Mark
Thomas
Ross
Andrew
Michelle
Adrian
Diane

I want to find the first occurrence of "Andrew", but also the
neighboring rows (like the two rows above the first "Andrew" and the
two rows below the first "Andrew"). This is an example of what I'd
like the query to return:

Daniel
Artur
Andrew
Mark
Thomas

Please keep in mind this is just a demo to illustrate my question;
this is not a real table example!
Thanks a lot!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Find neighboring rows

2009/11/8 Andrew Tran :
> I want to find the first occurrence of "Andrew", but also the
> neighboring rows (like the two rows above the first "Andrew" and the

There is a conceptual issue with what you are trying to accomplish.
What are "the rows above" and "below"?
According to relational theory, entities are grouped into sets, and
thus, without any implicit order.
So, if you want them by alphabetic order, it is ok, if you expect them
"in the order you inserted them", you should explicit this through an
additional timestamp or auto_increment column, as records are not
guaranteed to be returned in that order.

Let's suppose that you want them on natural order, your query could be
done with something like this:
SET @name = 'whatever';
SELECT n
FROM names
WHERE n >= @name
ORDER BY n ASC
LIMIT 3
) UNION ALL (
SELECT n
FROM names
WHERE n < @name
ORDER BY n DESC
LIMIT 2
) ORDER BY n;

Row has been named n; table: names. Unexpected results if @name does not exist.

If you expect a lot of records, expect also bad performance with this
query: add an index to speed up the orderings; also several
performance optimizations could be applied depending on your case.

--
Jaime Crespo
MySQL & Java Instructor
Warp Networks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org