|Subject||Re: [firebird-support] Help needed with grouping, GROUP BY and LIST()|
Hi Sean, thank you for your answer.
Yes, the same information should not be repeated within a single 'field'. The second "HIGH_FLOW" characteristic is only in the original result set because it has 2 properties (> 10 litres / sec and > 50 gals / hour - sorry contrived I know!) via the J_CHAR_PROPERTY table.
Some questions before I can give a suggestion:
> TEST_ID JFI_ID F_NAME N1_NAME N2_NAME MODE CHARACTERIST
> IC PROPERTY
> 1 1 FLOW_1 NODE_A NODE_B MODE_1 HIGH FLOW > 10 litres /
> 1 1 FLOW_1 NODE_A NODE_B MODE_1 HIGH FLOW > 50 gals /
> 1 1 FLOW_1 NODE_A NODE_B MODE_1 LOW LOSS Pump current <
> 2 2 FLOW_2 NODE_C NODE_D MODE_1 LOW FLOW < 5 litres / sec
> 44 3 FLOW_1 NODE_C NODE_D MODE_2 LOW FLOW < 5 litres / sec
> Q1. How can I group and LIST() to produce a single row for the first test case
> (FLOW_1 from NODE_A to NODE_B with MODE_1). The result would look
> something like this:
> 1 1 FLOW_1 NODE_A NODE_B MODE_1 HIGH_FLOW, LOW_LOSS >
> 10 litres / sec, > 50 gals / hour, Pump current < 30
Q: Did you mean to exclude the 2nd "HIGH_FLOW" characteristic value from the summary?
It would be nice, but not essential. Currently the 'properties' of a 'characteristic' are not ordered to keep the example simple, but I could add a SEQ field to define the ordering and in that case it would be good for the LIST() to observe the order.
Q: Do you require the relationship/relative position between the "characteristic" and "property" values be maintained in the summary result?