Query next unique id ordered by another field.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Query next unique id ordered by another field.

Post 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 :)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Code: Select all

SELECT id
FROM table
WHERE id > '004'
ORDER BY id
LIMIT 1
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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.
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post 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=&quote;001&quote; ???
ORDER BY item
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Code: Select all

SELECT *
FROM table
WHERE item > 'bike'
ORDER BY item
LIMIT 1
Post Reply