-
Horace's Friday Code Quiz
As you know, I'm not very good at this coding lark (but I'm trying)
Here's the request..............
We want the most recent (latest date_created or, where it isn't null, the date_updated) gross_family_income for each hurn on the W_Person table. W_Lifestyle only has purn on it. Certain lifestyle values are not acceptable (the where clauses).
Here's my code (which doesn't work!!).................
SELECT w_person.hurn,
CASE WHEN w_lifestyle.date_updated IS NULL
THEN w_lifestyle.date_created
ELSE w_lifestyle.date_updated END highest_date,
w_lifestyle.gross_family_income,
row_number() over
(PARTITION BY hurn ORDER BY highest_date desc) rn
from w_lifestyle, w_person
where w_lifestyle.purn = w_person.purn
and w_lifestyle.gross_family_income is not null
and w_lifestyle.gross_family_income <> 'Z'
and w_lifestyle.gross_family_income <> 'X'
and w_lifestyle.gross_family_income <> ' '
order by highest_date
where rn = 1
(If you take out the rownum bit it flows through but gives me lots of rows when what I want is one.)
Anyone bored/competent enough to help with this will be awarded a free pint....
Much obliged.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|