Annie Jeba Annie Jeba - 1 month ago 9
SQL Question

First Value Function to return null in Oracle

I have a requirement to fetch first result.

I'm using the below query for performing the operation

select id,firstname,dob,assignment
from (
select table1.id as id,
table1.name as firstname,
table1.dob as dob,
First_value(table2.Res)over (partition by table2.id order by table2.date asc) f,
table2.Res as assignment
from table1,table2
where table1.id = table2.id and
) where assignment = f


Lets assume there are no records in table2. In that case my expected output should be

id firstname dob assignment
1 Ana 23/03/1960 null


The query works fine if there is a record in table2. But the above query is not returning any results if there are no records for a particular employee in table2.

Answer

Try this :

SELECT id,firstname,dob,assignment
FROM (
       SELECT table1.id as id, 
              table1.name as firstname,
              table1.dob as dob,
              First_value(table2.Res)over(partition by table2.id order by table2.date asc) f,
              table2.Res as assignment
       FROM table1
       LEFT JOIN table2 ON table1.id  = table2.id 
 ) WHERE ( assignment = f OR ( assignment IS NULL AND f IS NULL) )
Comments