SELECT all the newest record distinct keyword with a non null value in one column

Question

Hello again...

Following on from this question http://stackoverflow.com/questions/1740199/select-the-newest-record-with-a-non-null-value-in-one-column

I know have a problem where I have this data

id | keyword | count | date
1 | ipod | 200 | 2009-08-02
2 | ipod | 250 | 2009-09-01
3 | ipod | 150 | 2009-09-04
4 | ipod | NULL | 2009-09-07
5 | apple | 100 | 2009-07-01
6 | apple | 98 | 2009-07-05
7 | apple | 500 | 2009-07-30
8 | itunes | NULL | 2009-08-30
9 | itunes | 50 | 2009-09-10
10 | itunes | NULL | 2009-09-15

And need a query which will fetch out rows 3, 7 and 9 Row which has the newest date and is non-null.

Answer 1

Use:

SELECT t.id,
       t.keyword,
       t.count,
       t.date
  FROM TABLE t
  JOIN (SELECT t.keyword,
               MAX(t.date) 'max_date'
          FROM TABLE t
         WHERE t.count IS NOT NULL
      GROUP BY t.keyword) x ON x.keyword = t.keyword
                           AND x.max_date = t.date