CODEWITHSUNDEEP
sqloracle-databaseanalytic-functions
user1557642
user1557642

Reputation: 1143

Oracle SQL Analytics Function FirstValue with 'NULL' Values

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

Upvotes: 1

Views: 4037

Answers (2)

Michael
Michael

Reputation: 21

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)

Upvotes: 2

Florin Ghita
Florin Ghita

Reputation: 17643

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

Upvotes: 1

Related Questions