Hi,
Richard Kurth wrote:
-----Original Message-----
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Friday, May 11, 2007 9:25 AM
To: Mogens Melander
Cc: mysql@lists.mysql.com
Subject: Re: finding next and prev record in mysql
Mogens Melander wrote:
On Fri, May 11, 2007 07:15, Richard Kurth wrote:
How would I find the next id and the prev id in sql statement like
the one below. The id number is not going to be in order so I can't
do a < or > limit 1 on the search
SELECT id FROM contacts WHERE category = '5' AND subcategory = '1'
AND members_id= '8' ORDER BY lastname
The PHP variant could look like (prev id):
Yes I read your article but I can't figure out how to use it with my
existing sql statement.
This is just an example of the search sql statement it could be different
than this.
I all ready know what Id number I what to find the prev and the next
recorded number around. Say the id number is 52. If I print out the list of
ids the one before number 52 is 503 the one after is 302. How can I use your
script to find these numbers using the same sql statement. I am just not
sure how to incorporate it in to the search.
Yes, now that I look more closely at your question it does require some tweaking
to work. I don't know your exact data, but I'll asume:
mysql> select * from contacts order by lastname;
+------+----------+-------------+---------------+
| id | category | subcategory | lastname |
+------+----------+-------------+---------------+
| 456 | 5 | 1 | everybody |
| 483 | 5 | 1 | me |
| 195 | 5 | 1 | them |
| 182 | 5 | 1 | todo el mundo |
| 290 | 5 | 1 | us |
| 44 | 5 | 1 | we |
| 365 | 5 | 1 | yall |
| 229 | 5 | 1 | you |
+------+----------+-------------+---------------+
The first thing to notice is category and subcategory are constants (both in my
data, and in your query, because you put them in the WHERE clause), so you can
factor them out. This makes the rest of the query easier to write, and then you
can plug back in the category and subcategory.
Suppose I'm currently on record 290, 'us'. Next is 'we' and prev is 'todo el
mundo.'
select
if(lastname > 'us', 'next', 'prev') as direction,
if(lastname > 'us', min(lastname), max(lastname)) as lastname
from contacts
where lastname <> 'us'
group by 1
order by if(lastname > 'us', 1, -1);
+-----------+---------------+
| direction | lastname |
+-----------+---------------+
| prev | todo el mundo |
| next | we |
+-----------+---------------+
Now you can add back in the other clauses:
select
if(lastname > 'us', 'next', 'prev') as direction,
if(lastname > 'us', min(lastname), max(lastname)) as lastname
from contacts
where lastname <> 'us' and category = 5 and subcategory = 1
group by 1
order by if(lastname > 'us', 1, -1);
If you need to get the id column back, I think you will need to join this result
set back to the original table to get other columns.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
