Horace's Friday Code Quiz

[Horace]

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.

[DaPi]

You need another SELECT wrapped round it.

(BTW: NVL(w_lifestyle.date_updated, w_lifestyle.date_created) is less typing than the CASE).

SELECT * FROM (
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 <> ' '
) where rn = 1

[Horace]

Haven't had time to test this yet, but thanks very much for your invaluable input.

So close..... Must be improving