How to select the first element of a collection being a column of a query-CodePudding

the table t has two column: a and b. a is an integer, b is collection.

I would like to to select for each line a and the first value of the collection b.

I tried this but it doesn't work

WITH
    s (a, b)
    AS
        (SELECT 1, 'ff' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 2, 'ee' FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, COLLECT (s.b)
             FROM s
         group BY s.a)
select t.a, t.b.first()
from t

[Error] Execution (42: 16): ORA-00904: "T"."B"."FIRST": invalid identifier

Is there a way to to do that?

code

CodePudding user response：

this query do the same things witout the collection method first.

WITH
    s (a, b)
    AS
        (SELECT 1, 'ff' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 2, 'ee' FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, COLLECT (s.b)
             FROM s
         GROUP BY s.a),
    v (a, b)
    AS
        (SELECT t.a, tb.b
           FROM t
                OUTER APPLY (SELECT x.COLUMN_VALUE     b
                               FROM TABLE (t.b) x
                              FETCH FIRST 1 ROW ONLY) tb)
SELECT *
  FROM v;

code