Meh. If your requirement is a single query, then you need to implement a linked list in the database. This isn't that hard, but it requires that all inserts and deletes update 3 rows vs. just selects doing three queries. This probably is ok.
CREATE TABLE images (
id INT
,prev_id INT
,next_id INT
,other_columns_here
);
Then, to get everything in a single query, you do:
SELECT * FROM images WHERE ( (id=?) OR (next_id=?) OR (prev_id=?) );
Not that hard. :-)
My criteria for good software:
- Does it work?
- Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
| [reply]
[d/l]
[select] |
-- changed "union" to "union all"
select id from table where id > ? order by id asc limit 1
UNION ALL
select id from table where id < ? order by id desc limit 1
... does not work in Sybase for (a) "order by" clause does not go with "union all" or in derived table creation; (b) there is no "limit" clause, just "rowcount" option per SQL session/transaction.
| [reply]
[d/l] |
SELECT *
FROM table
WHERE id=(SELECT id FROM table WHERE id>? ORDER BY id ASC LIMIT 1)
OR id=(SELECT id FROM table WHERE id<? ORDER BY id DESC LIMIT 1)
Careful with that hash Eugene.
| [reply]
[d/l] |