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

Question

Hi there

I have table data which looks like this

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

Now what I am after is getting the count of the row which has the newest date but has a not null count. In which case row 3 with count 150.)

eg

SELECT `keyword`, `count` , max( `date` ) 
FROM `keywords` 
WHERE keyword = "ipod"
AND `count` IS NOT NULL 
GROUP BY keyword

This returned the right date but not the right count (returned 200). I also tried doing a left join on it's self.

SELECT `t1`.`keyword` , `t2`.`count` , max( `t1`.`id` )
FROM `keywords` `t1`
LEFT JOIN `keywords` `t2` ON `t1`.`id` = `t2`.`id`
WHERE `t1`.`keyword` = 'ipod'
AND `t1`.`count` IS NOT NULL
GROUP BY `t1`.`keyword`

And this did get the max id but it didn't left-join onto that like I hoped and returned only 200.

Help?

Answer 1

SELECT `keyword`, `count` , `date`
FROM `keywords` 
WHERE keyword = "ipod"
AND `count` IS NOT NULL 
order by date desc 
limit 1

Answer 2

I'm sure there's a better way, but in the mean time:

SELECT `count`
FROM `keywords` 
WHERE keyword = "ipod"
AND `count` IS NOT NULL 
ORDER BY `date` DESC
LIMIT 1

Should fit your requirements. Note that LIMIT is not portable SQL, so it will only work for MySQL. The ORDER BY clause is necessary to force the database to sort by, well, date.

When I mentioned that there should be a better way, that's merely because of the way the LIMIT clause works, which is: The database has to fetch ALL rows that meet the query criteria, then simply truncates the result set to the desired amount specified by LIMIT.

Answer 3

Does this work for you:

SELECT `keyword`, `count`, `date` 
FROM `keywords` 
WHERE keyword = "ipod"
AND `count` IS NOT NULL 
ORDER BY DATE DESC
LIMIT 1