Oracle SQL Analytics Function FirstValue with 'NULL' Values

Question

I have an issue with the analytics function FirstValue:(Syntax:

FIRST_VALUE(TAble1.Column2 IGNORE NULLS) OVER (PARTITION BY Column1 ORDER BY Column3 DESC)

Example:

Column1     Column2              Column3 
1               A             01/01/2012
1               (NULL)        02/01/2012
1               (NULL)        03/01/2012

I want to retrieve one line using the analytics function describe above.

Column1     Column2              Column3 
1               A             01/01/2012

The issue is that Oracle retrieve 2 lines, one with Null and one other with the value'A' in the column2

Could you please help me to solve this issue?

Best Regards

put your entire query or the relevant code. – Florin Ghita Jul 27 '12 at 12:49 — Florin Ghita, Jul 27 '12 at 12:49

score 2 · Answer 1 · answered Sep 10 '14 at 01:13

This question is a little old, but posting the answer in case anyone else is Googling and completely stuck.

Oracle's default windowing behaviour is to blame here.

Place

range between unbounded preceding and unbounded following

after your order by clause

That is,

FIRST_VALUE(TAble1.Column2 IGNORE NULLS) OVER (PARTITION BY Column1 ORDER BY Column3 DESC range between unbounded preceding and unbounded following)

Florin Ghita · Answer 2 · 2012-07-27T14:30:21.663

1

That happens because the position of first non null value in the group differs on column1 to column2.

The combination of first_value function with distinct is not the solution for this type of query.

You can use the row_number function instead:

select * from (
    select
      row_number() OVER (PARTITION BY Column1 ORDER BY Column3 DESC) as rnk,
      FIRST_VALUE(TAble1.Column1 IGNORE NULLS) 
          OVER (PARTITION BY Column1 ORDER BY Column3 DESC) as column1,
      FIRST_VALUE(TAble1.Column2 IGNORE NULLS) 
          OVER (PARTITION BY Column1 ORDER BY Column3 DESC) as column2,
    column3
    from your table
)
where rnk = 1

edited Jul 27 '12 at 14:30

answered Jul 27 '12 at 12:45

Florin Ghita

17,525
6
57
76

i would like to use the function firstvalues – user1557642 Jul 27 '12 at 13:04
edit the question and put your entire select. the result should have 3 lines, not two. – Florin Ghita Jul 27 '12 at 13:05
it must be because i have a "distinct" option in my query. My select is select DISTINCT TM_TRFATM.SEQGRP, FIRST_VALUE(RF_ADRPST.NUMIDVRIF IGNORE NULLS) OVER (PARTITION BY TM_TRFATM.SEQGRP ORDER BY RF_ADRPST.DATDERMDFADRPST DESC) NUMIDVRIF, FIRST_VALUE(RF_ADRPST.INDDMGALC IGNORE NULLS) OVER (PARTITION BY TM_TRFATM.SEQGRP ORDER BY RF_ADRPST.DATDERMDFADRPST DESC) INDDMGALC from REFRIF.TM_TRFATM TM_TRFATM, REFRIF.RF_ADRPST RF_ADRPST where (RF_ADRPST.NUMIDVRIF=TM_TRFATM.NUMIDVRIF) – user1557642 Jul 27 '12 at 13:14
Sorry, too little time to understand the relation with question. Please adapt the query to the example in the question. You can edit your question. – Florin Ghita Jul 27 '12 at 13:29

Oracle SQL Analytics Function FirstValue with 'NULL' Values

2 Answers2