- Select next element to current by the value of some field
- Select records that are unique by some complex expression
- Select all records that have the same parameter as given record.
Let's say we have the list of elements in `elements` table and we have the id of some element in this table. Now we have to get the next element from the list ordered by `sort_field`. That could be easily done with the nested select statement:
Looks not very cool. Let's do the select statement from `element` table twice to avoid nesting:
select * from elements e where e.sort_field >= (select * from elements where element_id = #value#) and e.element_id != #value# order by e.sort_field limit 1;
Do not scare of double select from single table. That is the same technique as if they were different tables.
-- The e1 is the result row and e2 is a helper row --that was picked up using nested select previously. select e1.* from elements e1, elements e2 --conditional statement will look like where e1.sort_field >= e2.sort_field and e1.element_id != e2.element_id and e2.element_id = #value# -- and the end of the query remains almost the same order by e1.sort_field limit 1