Partition, row_number, and where clause problem
416414Aug 16 2006 — edited Aug 17 2006Hi folks,
I am getting frustrated trying to solve something which is probably very easy. Maybe someone can help me out?
Here's the problem - I want to exclude rows "where an expire_date is not null". Whenever I put that type of code in the following SQL, it generates errors. The following clean code and the results from this are:
(select diagnosis_code, description_txt, effective_date, expire_date,
row_number() over (partition by diagnosis_code
order by diagnosis_code ASC, effective_date DESC) as rn
From diagnosis_code)
Using 2 rows, the results I get are:
diagnosis_code; description_txt; effective_date; expire_date; rn
001.1; desc for 001.1; 01-jan-97; null; 1
001.1; desc for 001.1; 01-Jan-90; 31-dec-96; 2
The problem is that I don't want to include the rows "where expire_date is not null".
I have not figured out the proper way to include this where clause in this SQL.
Can anyone help me out?
Janet