Page 1 of 1
Query next unique id ordered by another field.
Posted: Fri Jun 03, 2005 7:03 am
by WaldoMonster
Code: Select all
id item
---------
001 bike
004 book
003 spoon
002 table
When I make a query ordered by item and select id = 004.
How do I retrieve the next id (id = 003)?
I use MySQL with PHP.
Thanks for your help

Posted: Fri Jun 03, 2005 10:18 am
by timvw
Code: Select all
SELECT id
FROM table
WHERE id > '004'
ORDER BY id
LIMIT 1
Posted: Fri Jun 03, 2005 10:58 am
by Burrito
you say get the next id from 004 and then provide an example of 003...obviously that's not next unless you're decrementing the ID value.
I'm pretty sure MySQL doesn't have a decrementing function that you can use in your query itself so you could incorporate some php into it.
ex:
Code: Select all
$id = 4;
$query = "select * from myTable where id = ".($id - 1);
I assume that you're using strings for your ID's (the leading zeros), so you'd need to tweak that a bit to make it work for you.
Posted: Fri Jun 03, 2005 11:33 am
by WaldoMonster
Sorry but this is not what I mend.
Maybe I haven't explained it right.
I think a few more examples will make it better to understand:
id=001 then the next id sorted on items is 004
id=004 then the next id sorted on items is 003
id=003 then the next id sorted on items is 002
id=002 then the next id sorted on items is <empty>
The query could be something like this:
Code: Select all
SELECT id
FROM table
WHERE ??? id="e;001"e; ???
ORDER BY item
Posted: Fri Jun 03, 2005 11:45 am
by Burrito
you'd have to create some kind of algorithm to do that as you're wrapping from 001 to 004. for the other you could just use "order by id desc" at the end of your sql.
problem is though that it's a string, so it won't order correctly. I'd suggest you change it to an int ... although that still won't take care of your wrapping issue.
Posted: Fri Jun 03, 2005 3:08 pm
by timvw
Code: Select all
SELECT *
FROM table
WHERE item > 'bike'
ORDER BY item
LIMIT 1