0

I am trying to get First value of removed_date for individual person by using following query:

 select p.person_id,
        FIRST_VALUE(pe.removed_date) over (order by  e.removed_date)
 from person p inner join placement_episode pe on p.person_id = pe.child_id

But this is returning first non zero value for all person like shown in the output:

  pid      first_removed_dt
 30120033   30-JAN-14
 30160046   30-JAN-14
 30160045   30-JAN-14
 30210037   30-JAN-14
 30210036   30-JAN-14

If I try to get last value using LAST_VALUE() function then its returning last value of removed date for individual person properly:

   pid     last_removal_date
 30120033   30-JAN-14
 30160046   20-AUG-14
 30160045   20-AUG-14
 30210037   11-FEB-15
 30210036   17-FEB-15

Why FIRST_VALUE() function is behaving like this? Is there any other way by which i can get first removal_date for individual person?

Madhusudan
  • 4,637
  • 12
  • 55
  • 86

2 Answers2

1

Updated, have a look on this below, query. Partition by should be the clause you need to use. You might need to correct some syntax .

Oracle 'Partition By' and 'Row_Number' keyword

WITH cte AS
(
    select p.person_id, pe.removed_date,    
    row_number() over(partition by pe.removed_date order by pe.removed_date) as rn
    from person p inner join placement_episode pe on p.person_id = pe.child_id

)
SELECT *
FROM cte
WHERE rn = 1
Community
  • 1
  • 1
Shaikh Muhammad Fahad
  • 33
  • 7
0

Use PARTITION BY, Something as following, need to modify to correct, https://msdn.microsoft.com/en-us/library/ms189461.aspx

;WITH cte AS
(
   SELECT *, ROW_NUMBER() OVER (PARTITION BY pe.removed_date ORDER BY e.removed_date DESC) AS rn
   FROM person p inner join placement_episode pe on p.person_id = pe.child_id

)
SELECT *
FROM cte
WHERE rn = 1
Fahad Shaikh
  • 9
  • 4
  • In my actual query I have 30+ fields other than removed_date. So I can't change everything – Madhusudan Jul 29 '15 at 04:14
  • Is your query for oracle database? While running this query I am getting an error saying 'FROM keyword not found where expected' – Madhusudan Jul 29 '15 at 04:34
  • As said , you might have to change some syntax or should setup a sqlfiddle. Glad you found solution using Partition By Clause. – Fahad Shaikh Jul 29 '15 at 04:55