Geek asks and answers

Order SQL results based on predecessor

My database has the following table:

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

with data samples:

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

I want the SQL query that selects nameand valueis ordered so that first there is an element with a predecessor null, and then a row with who the predecessor is equal to the name of this row, the next and so on (i.e. buyers, mixers, cook, there is).

The order is strictly linear - behavior if two lines have the same value of the predecessor undefined.

I am trying to find an SQL query that will produce the ordering that I want. I don’t know where to start.

I am using an Informix database, although any SQL option would be a useful starting point.

updated to reflect the fact that the ordering is not in alphabetical order

+3
4

Informix WITH, WITH RECURSIVE. . . , , , , ( - ) .

, . , . . , , "".

CREATE TEMP TABLE Flattened
(
    Hierarchy   SERIAL,
    Level       INTEGER,
    Name        VARCHAR(25),
    Value       INTEGER,
    Predecessor VARCHAR(25)
);

CREATE TEMP TABLE Intermediate
(
    Hierarchy   SERIAL,
    Level       INTEGER,
    Name        VARCHAR(25),
    Value       INTEGER,
    Predecessor VARCHAR(25)
);

INSERT INTO Flattened(Hierarchy, Level, Name, Value, Predecessor)
   SELECT 0, 0, name, value, predecessor
     FROM Anonymous
    WHERE Predecessor IS NULL;

WHILE ...any rows were inserted into table...
    INSERT INTO Intermediate(Hierarchy, Level, Name, Value, Predecessor)
        SELECT F.Hierarchy, F.Level + 1, A.Name, A.Value, A.Predecessor
          FROM Flattened AS F, Anonymous AS A
         WHERE F.Name = A.Predecessor
           AND F.Level = (SELECT MAX(Level) FROM Flattened);
    INSERT INTO Flattened SELECT * FROM Intermediate;
    DELETE FROM Intermediate;
END WHILE

DROP TABLE Intermediate;

:

SELECT Name, Value, Predecessor
  FROM Flattened
 ORDER BY Hierarchy, Level, Name;

- , . , , :

SELECT COUNT(*) INTO n_count FROM Flattened;


LET o_count = n_count - 1;
WHILE o_count != n_count
    ...as above - two INSERT operations and a DELETE operation
    LET o_count = n_count;
    SELECT COUNT(*) INTO n_count FROM Flattened;
END WHILE;

, ( ).

BEGIN;

CREATE TABLE Anonymous
(
     Name        VARCHAR(25),
     Value       INTEGER,
     Predecessor VARCHAR(25)
);

INSERT INTO Anonymous VALUES("buyingredients", 10, NULL);
INSERT INTO Anonymous VALUES("eat", 3, "cook");
INSERT INTO Anonymous VALUES("cook", 12, "mixingredients");
INSERT INTO Anonymous VALUES("mixingredients", 5, "buyingredients");

CREATE PROCEDURE Flatten_Anonymous()

    DEFINE old_count INTEGER;
    DEFINE new_count INTEGER;

    CREATE TEMP TABLE Flattened
    (
        Hierarchy   SERIAL,
        Level       INTEGER,
        Name        VARCHAR(25),
        Value       INTEGER,
        Predecessor VARCHAR(25)
    );

    CREATE TEMP TABLE Intermediate
    (
        Hierarchy   SERIAL,
        Level       INTEGER,
        Name        VARCHAR(25),
        Value       INTEGER,
        Predecessor VARCHAR(25)
    );

    INSERT INTO Flattened(Hierarchy, Level, Name, Value, Predecessor)
       SELECT 0, 0, name, value, predecessor
         FROM Anonymous
        WHERE Predecessor IS NULL;

    SELECT COUNT(*) INTO new_count FROM Flattened;
    LET old_count = new_count - 1;
    WHILE old_count != new_count
        INSERT INTO Intermediate(Hierarchy, Level, Name, Value, Predecessor)
            SELECT F.Hierarchy, F.Level + 1, A.Name, A.Value, A.Predecessor
              FROM Flattened AS F, Anonymous AS A
             WHERE F.Name = A.Predecessor
               AND F.Level = (SELECT MAX(Level) FROM Flattened);
        INSERT INTO Flattened SELECT * FROM Intermediate;
        DELETE FROM Intermediate;
        LET old_count = new_count;
        SELECT COUNT(*) INTO new_count FROM Flattened;
    END WHILE

    DROP TABLE Intermediate;

END PROCEDURE;

EXECUTE PROCEDURE Flatten_Anonymous();

SELECT Name, Value, Predecessor
  FROM Flattened
 ORDER BY Hierarchy, Level, Name;

DROP TABLE Flattened;

ROLLBACK;

:

buyingredients     10
mixingredients      5  buyingredients
cook               12  mixingredients
eat                 3  cook

: Informix 11.70.FC2 MacOS X 10.6.7.

, .

+2

Transact-SQL :

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

, Informix , , , , , Transact-SQL.

+2

Informix, SQL .

Informix, SQL Server. @T - , . , CTE.

declare @T table
(
  name varchar(25),
  value int,
  predecessor varchar(25)
)  

insert into @T
select 'buyingredients', 10, null union all
select 'cook',           12, 'mixingredients' union all
select 'mixingredients',  5, 'buyingredients' union all
select 'eat',             3, 'cook'

;with cte as 
(
  select T.name,
         T.value,
         T.predecessor,
         1 as sortorder
  from @T as T
  where T.predecessor is null
  union all
  select T.name,
         T.value,
         T.predecessor,
         C.sortorder+1 as sortorder
  from @T as T
    inner join cte as C
      on T.predecessor = C.name
)
select C.name,
       C.value,
       C.predecessor
from cte as C
order by C.sortorder

You can try here: http://data.stackexchange.com/stackoverflow/q/102534/recursive-cte-to-build-a-chain

Edit

I still don't know anything about Informix, but maybe you can do something using the Node DataBlade Module

+1
source

I think the way to do what you want is to create a temporary table with a depth field for each element, and then use this depth field to organize the results.

0
source

More articles:


All Articles