I have problem. Below is a set of data I am working with and I only need the record with the newest date. I used this script with MAX(assessdate1) but I am still getting 2 or more dates. I thought if MAX is used then you would only get the most recent date. Thanks ahead of time for for answering.
WFD1BDB
Select account, max(assessdate1), household, dependents, income from clcharity
where (income < 20420 and household = 1)-- or (income < 27380 and household =2)
group by account, assessdate1, household, dependents, income
order by account
199312 2001-07-01 00:00:00.000 1 0 5808
199312 2004-12-15 00:00:00.000 1 0 6768
199510 2001-07-01 00:00:00.000 1 NULL 6540
199510 2007-12-07 00:00:00.000 1 0 7476
199822 2001-07-01 00:00:00.000 1 NULL 6612
199860 2001-07-01 00:00:00.000 1 NULL 13000
199860 2004-08-02 00:00:00.000 1 0 17000
199860 2004-10-25 00:00:00.000 1 0 17000
WFD1BDB
Select account, max(assessdate1), household, dependents, income from clcharity
where (income < 20420 and household = 1)-- or (income < 27380 and household =2)
group by account, assessdate1, household, dependents, income
order by account
199312 2001-07-01 00:00:00.000 1 0 5808
199312 2004-12-15 00:00:00.000 1 0 6768
199510 2001-07-01 00:00:00.000 1 NULL 6540
199510 2007-12-07 00:00:00.000 1 0 7476
199822 2001-07-01 00:00:00.000 1 NULL 6612
199860 2001-07-01 00:00:00.000 1 NULL 13000
199860 2004-08-02 00:00:00.000 1 0 17000
199860 2004-10-25 00:00:00.000 1 0 17000