RE: [firebird-support] Help needed with grouping, GROUP BY and LIST()

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Mike,

What’s performance like?


Sean

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: August 28, 2016 5:25 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Help needed with grouping, GROUP BY and LIST()



Sean,


Have a look at my reply to your second question, about using CTE, that will be 
part of your answer for this problem as well.

FYI: LIST() doesn't observe ORDER BY...
Thanks to your hint and example CTE I was able to get my query fully working as 
follows. Sorry, there are a couple of name changes since the original question 
and I dispensed with the J_RESULT_GROUP table as a test can only belong to 0 or 
1 groups.

-- Create the CTE
with

-- CTE 1 is for a flow instance
FLOW_INSTANCE(FI_ID, F_NAME, F_PATH)
as
(
    select jfi.ID, f.NAME, n1.NAME || ' to ' || n2.NAME
    from J_FLOW_INSTANCE jfi
    join FLOW f on f.ID = jfi.FLOW
    join NODE n1 on n1.ID = jfi.SRC
    join NODE n2 on n2.ID = jfi.DEST
),

-- CTE 2 is for a flow test
FLOW_TEST(FT_ID, FI_ID, F_NAME, F_PATH, FT_MODE, RES_CHAR, RES_PROP, TGROUP)
as
(
    select jft.ID, fi.FI_ID, fi.F_NAME, fi.F_PATH, m.NAME, c.NAME, p.NAME, 
jft.TGROUP
    from J_FLOW_TEST jft
    join FLOW_INSTANCE fi on fi.FI_ID = jft.INSTANCE
    join MODE m on m.ID = jft.MODE
    join J_TEST_RESULT_CHARS jtrc on jtrc.TEST = jft.ID
    join CHARACTERISTIC c on c.ID = jtrc.RESULT
    join J_CHAR_PROPERTY jcp on jcp.CHARACTERISTIC = c.ID
    join PROPERTY p on p.ID = jcp.PROPERTY
)

-- Now the main query, which is the union of those tests that do not belong to 
any group and the grouping of those that are in a group

-- 1) Find all test results that have not been added to a group 
(FLOW_TEST.TGROUP = null) and combine them by test ID
select ftr_ng.FT_ID as FT_ID,
        ftr_ng.FI_ID as FI_ID,
        ftr_ng.F_NAME as F_NAME,
        ftr_ng.FT_MODE,
        ftr_ng.F_PATH as FI_PATH,
        list(distinct ftr_ng.RES_CHAR) as RES_CHARS,
        list(distinct ftr_ng.RES_PROP) as RES_PROPS,
        'No group' as T_GROUP
from FLOW_TEST ftr_ng
where ftr_ng.TGROUP is null
group by 1, 2, 3, 4, 5

union

-- 2) Find all the test results that are in a group and combine each group into 
a single row
select  list(distinct ftr_g.FT_ID) as FT_ID,
        list(distinct ftr_g.FI_ID) as FI_ID,
        list(distinct ftr_g.F_NAME) as F_NAME,
        ftr_g.FT_MODE,
        list(distinct ftr_g.F_PATH) as FI_PATH,
        list(distinct ftr_g.RES_CHAR) as RES_CHARS,
        list(distinct ftr_g.RES_PROP) as RES_PROPS,
        ftr_g.TGROUP
from FLOW_TEST ftr_g
where ftr_g.TGROUP is not null
group by 4, 8

Thanks again,

Mike