Ordering SQL results based on predecessor

Question

I have the following table in my database:

create table (
    name        varchar(25),
    value       int,
    predecessor varchar(25)
);

with sample data:

name           | value | predecessor
---------------+-------+---------------
buyingredients | 10    | null
eat            |  3    | cook
cook           | 12    | mixingredients
mixingredients |  5    | buyingredients

I want an SQL query that selects name and value, ordered so that the item with the predecessor null is first, and then the row with who's predecessor is equal to that row's name is next and so on (i.e. buyingredients, mixingredients, cook, eat).

The ordering is strictly linear - the behaviour if two rows have the same value of predecessor is undefined.

I'm struggling to find an SQL query that will produce the ordering I want. I don't really know where to start.

I'm using an Informix database, though any variant of SQL would be a useful starting point.

updated to reflect the fact the ordering is not alphabetical

Answer 1

In Transact-SQL with common table expressions:

WITH LinkedList (name, value, predecessor, ordering)
AS
(
    SELECT a.name, a.value, a.predecessor, 0
    FROM   YourTable a
    WHERE  a.predecessor IS NULL
    UNION ALL
    SELECT b.name, b.value, b.predecessor, c.ordering + 1
    FROM   YourTable b
    INNER JOIN LinkedList c
    ON     b.predecessor = c.name
)
SELECT d.name, d.value
FROM   LinkedList d
ORDER BY d.ordering, d.name

I don't know whether Informix has this construct, but what you are asking is in essence a recursive query, which common table expressions give you in Transact-SQL.