Need columns in attributelist but not in the group by -expression

[Orca777]

Hi,

i want A.FK_DATEI_DE,
A.FK_DATEI_EN,
A.FK_DATEI_MU*/

in the attribute-list ( matches max(A.LIEFERDATUM))
but not in the group by-expression,
any idea

select distinct
A.FK_BERICHTSART,
B.NAME,
I.ISIN,
IE.BOERSENKUERZEL,
A.GESCHAEFTSJAHR,
A.FK_BERICHTSZEITRAUM,
B.FK_ENDEGESCHAEFTSJAHR,
/*A.FK_DATEI_DE,
A.FK_DATEI_EN,
A.FK_DATEI_MU,*/
MAX(A.LIEFERDATUM) as ALIEF
from BERICHT A,
EMITTENT_RESY B,
INSTRUMENT I,
INSTRUMENT_EXT IE
where A.FK_STATUS >= 3
and A.FK_EMITTENT = B.KUNDENNR
and I.PRIMAERINSTRUMENT = 1 -- das soll bewirken, dass nur das jeweilige Primärinstrument berücksichtigt wird
and I.FK_EMITTENT = A.FK_EMITTENT
and I.INSTRUMENTID = IE.FK_INSTRUMENT(+)
and 'DE0005003404' IN (SELECT ISIN FROM INSTRUMENT WHERE FK_EMITTENT = A.FK_EMITTENT)
group
by B.NAME,
I.ISIN,
IE.BOERSENKUERZEL,
A.FK_BERICHTSART,
A.GESCHAEFTSJAHR,
A.FK_BERICHTSZEITRAUM,
B.FK_ENDEGESCHAEFTSJAHR/*,
A.FK_DATEI_DE,
A.FK_DATEI_EN,
A.FK_DATEI_MU*/
order by ALIEF DESC

[nandu]

For the expressions you have grouped if these columns
A.FK_DATEI_DE, A.FK_DATEI_EN, A.FK_DATEI_MU will have only one value, or if you will only require the maximum or minimum value then u can use the MAX() or the MIN() for these columns in conjunction with the other column

[Orca777]

Originally posted by nandu
u can use the MAX() or the MIN() for these columns in conjunction with the other column

This worked !!
Thanks nandu
Orca

select distinct
A.FK_BERICHTSART,
B.NAME,
I.ISIN,
IE.BOERSENKUERZEL,
A.GESCHAEFTSJAHR,
A.FK_BERICHTSZEITRAUM,
B.FK_ENDEGESCHAEFTSJAHR,
MAX(to_char(A.LIEFERDATUM, 'YYYY.MM.DD HH24:MI') || ';' || a.fk_datei_de || ';' || a.fk_datei_mu || ';' || a.fk_datei_en ) as ALIEF
from BERICHT A,
EMITTENT_RESY B,
INSTRUMENT I,
INSTRUMENT_EXT IE
where A.FK_STATUS >= 3
and A.FK_EMITTENT = B.KUNDENNR
and I.PRIMAERINSTRUMENT = 1 -- das soll bewirken, dass nur das jeweilige Primärinstrument berücksichtigt wird
and I.FK_EMITTENT = A.FK_EMITTENT
and I.INSTRUMENTID = IE.FK_INSTRUMENT(+)
and 'DE0005003404' IN (SELECT ISIN FROM INSTRUMENT WHERE FK_EMITTENT = A.FK_EMITTENT)
group
by B.NAME,
I.ISIN,
IE.BOERSENKUERZEL,
A.FK_BERICHTSART,
A.GESCHAEFTSJAHR,
A.FK_BERICHTSZEITRAUM,
B.FK_ENDEGESCHAEFTSJAHR
order by ALIEF DESC

[marist89]

Maybe you can use an inline view and put your group by query in the view and join it back to the results you want outside the view...

[Orca777]

Originally posted by marist89
Maybe you can use an inline view and put your group by query in the view and join it back to the results you want outside the view...

Yes, this is a possible technique too, but in this case i have problems to identify the unique row with the max-val.
Orca

[nandu]

You don't need to convert to to_char etc., It can be

select distinct
A.FK_BERICHTSART,
B.NAME,
I.ISIN,
IE.BOERSENKUERZEL,
A.GESCHAEFTSJAHR,
A.FK_BERICHTSZEITRAUM,
B.FK_ENDEGESCHAEFTSJAHR,
Max(A.FK_DATEI_DE),
Max(A.FK_DATEI_EN),
Max(A.FK_DATEI_MU),
MAX(A.LIEFERDATUM) as ALIEF
from BERICHT A,
...
...
...

[Orca777]

No nando this does not work correct for my reason :

I want the additional coulums of that ONE ROW which
has the max-val and not the max-val of the GROUP;
So it's ok!!
Orca