How to get latest inserted value on not grouped column?

Question

How get MY_LATEST_INSERTED_NUMBER_BASING_ON_MY_DATE_COLUMN_FOR_ID_CONTEXT in more efficient and elegant way from below query ?

SELECT MAX(MY_DATE_COLUMN),
 ID,
 listagg(SOME_COLUMN, '') within GROUP (ORDER BY MY_DATE_COLUMN),
 SUBSTR(
      listagg(MY_NUMBER, ';') within GROUP (ORDER BY MY_DATE_COLUMN DESC),
      1,
      instr(
             listagg(MY_NUMBER, ';') within GROUP (ORDER BY MY_DATE_COLUMN DESC),'-') - 1
           ) as MY_LATEST_INSERTED_NUMBER_BASING_ON_MY_DATE_COLUMN_IN_ID_CONTEXT
 FROM MY_TABLE
 GROUP BY ID;

score 1 · Accepted Answer · answered Mar 14 '14 at 12:33

1

If I understand correctly, you want keep:

SELECT MAX(MY_DATE_COLUMN), ID,
       listagg(SOME_COLUMN, '') within GROUP (ORDER BY MY_DATE_COLUMN),
       max(MY_LATEST_INSERTED_NUMBER_BASING_ON_MY_DATE_COLUMN_IN_ID_CONTEXT)
           keep (dense_rank first order by MY_DATE_COLUMN DESC)
FROM MY_TABLE
GROUP BY ID;

The ALL CAPS don't really help convey what you want to do.

answered Mar 14 '14 at 12:33

Gordon Linoff

1,242,037
58
646
786

1

Astrologers proclaim the week of `KEEP` on SO. :) – Yaroslav Shabalin Mar 14 '14 at 12:40
it is certainly more elegant and despite the same cost I suppose that the query will be faster in production environment. Thanks. – rtbf Mar 14 '14 at 12:46

How to get latest inserted value on not grouped column?

1 Answers1