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

__._,_.___

---

Posted by: Mike Ro <miker169@gmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (7)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit Your Group

• New Members 5

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___