CODEWITHSUNDEEP
sqloracle-databasewindow-functions
Brandon Lehman
Brandon Lehman

Reputation: 69

FIRST_VALUE not working as I'd expect with PARTITION BY

Here is my test set:

master_ref  ref          value
56279276    56279325    FRAME ASSEMBLY1
56279276    384062724   FRAME ASSEMBLY2
56279276    443450775   FRAME ASSEMBLY3

I want to retrieve the value field based on the highest ref given a master_ref.

Here is what I tried that just return everything:

select first_value(value) over (partition by ida3masterreference order by ida3a4 desc) value, ida3masterreference, ida3a4 from sts_epm_title1;

I expected to only get one result:

master_ref    ref         value
56279276    443450775   FRAME ASSEMBLY3

But it still returns all 3 results. What am I doing wrong? Thanks!

Upvotes: 1

Views: 1315

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

first_value() is an analytic function, so it does not reduce the number of rows. You apparently want an aggregation function so use the keep syntax:

select max(value) keep (dense_rank first order by ida3a4 desc) as value, 
       ida3masterreference, max(ida3a4) as ida3a4
from sts_epm_title1
group by ida3masterreference

Upvotes: 3

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

You could use ROW_NUMBER/RANK:

WITH cte AS (
   select row_number() over (partition by ida3masterreference 
                             order by ida3a4 desc) AS rn, t.*
   from sts_epm_title1 t
)
SELECT *
FROM cte
WHERE rn = 1;

Upvotes: 0

Related Questions