[SOLVED] Get next/previous row from database, ordered by name (not id)

[atomicrabbit]

Ok I found this which basically says you can get the next and previous row using the following sql statement:

 

// next row
SELECT id FROM table WHERE id > 123 ORDER BY id ASC LIMIT 1
// prev row
SELECT id FROM table WHERE id < 123 ORDER BY id DESC LIMIT 1

 

but this will only work if you're ordering the results by the id. What if the results are ordered by another column? Is there another way to get the next/previous entries? Maybe a statement that returns results starting from a certain point. Like:

 

SELECT id FROM table ORDER BY product_name STARTING AT product_name="product123" LIMIT 2

 

obviously "STARTING AT" isn't a MySQL command, but maybe someone knows a command like it.

 

So if the table contained the following entries

 

id     product_name
----------------------
12     abc123
1      bob products
3      product123
16     chocolate
2      stuff

 

it would return:

id     product_name
----------------------
3      product123
2      stuff

[Philip]

By using LIMIT with an offset:

 

 

SELECT name FROM table ORDER BY name ASC LIMIT 2, 1

or:

SELECT name FROM table ORDER BY name ASC LIMIT 1 OFFSET 2

 

Same thing.

 

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be non-negative integer constants (except when using prepared statements).

 

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

 

mysql> SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.

[atomicrabbit]

right I know Limit can use an offset, but what if I don't know what the offset is? your "LIMIT X, Y" would get Y row(s) starting at the Xth row. But I don't what X is until the result comes back because I have much more than 5 rows in the database and its always changing.

 

So in the case of my example in my original post, I want to find the Next and Previous rows if Product123 is selected, and the results must be ordered by the product name.

 

So Next should = stuff and Previous should = chocolate

 

And as far as I know, the X in the limit command is related to the index in the returned results, not the actual auto-incrementing index (or 'id' column) of the database, otherwise it would be easy, I would just do LIMIT id, 1

 

Do you understand?

[kickstart]

Hi

 

Doing next and previous as 2 statement:-

 

SELECT * FROM table WHERE product_name>"product123" ORDER BY product_name LIMIT 1

 

SELECT * FROM table WHERE product_name<"product123" ORDER BY product_name DESC LIMIT 1

 

All the best

 

Keith

[atomicrabbit]

oh... you can do a > or < operation on a string?? I didn't know that.

[atomicrabbit]

what if I want to order the results by a "type" field that may have duplicate entries. Using the Where type>"typeA" won't work, because if there are 5 "TypeA" types, the WHERE statement will get the next different type rather than the next row ordered by the 'type' field. Is there anything I can do in that case?

[kickstart]

Hi

 

That I know of, not easily.

 

However you would probably want to be ordering by other fields as well. For example, say you would order it in type order, and then within record id within type order.

 

SELECT * FROM table WHERE (type>'$lastType') OR (type='$lastType' AND id > $lastId) ORDER BY type, id LIMIT 1

 

SELECT * FROM table WHERE (type<'$lastType') OR (type='$lastType' AND id < $lastId) ORDER BY type DESC, id DESC LIMIT 1

 

All the best

 

Keith

[atomicrabbit]

that makes sense., but couldn't you simplify the statement?:

 

SELECT * FROM table WHERE type>='$lastType' AND id > $lastId ORDER BY type, id LIMIT 1

 

SELECT * FROM table WHERE type<='$lastType' AND id < $lastId ORDER BY type DESC, id DESC LIMIT 1

 

because (in the case of the first statement) regardless if the 'type' is > or = the $lastType, the id should always be > the $lastId ... right?

[kickstart]

Hi

 

No, afraid not.

 

Say the data was

 

Id, Type

1, TypeA

2, TypeA

3, TypeA

4, TypeB

5, TypeB

6, TypeB

7, TypeA

8, TypeA

9, TypeA

10, TypeA

 

If you ordered by Type then ID, the records would be returned in order 1,2,3,7,8,9,10,4,5,6. If you tried your mod it would never return the TypeB records (4,5 and 6), as the last ID displayed by the time it got to them would have been 10.

 

All the best

 

Keith

[atomicrabbit]

ah, good point. THanks. And by the way. It worked