CODEWITHSUNDEEP
mysqlsqlselectwhere-clause
LarryFisherman
LarryFisherman

Reputation: 322

Get latest record

I'd like to select the latest row in my table based on a date field.

The latest date should be pulled out. The catch, however, is that if the date is NULL, that should be prioritised over any max(end_date).

The closest I've got is something like the following:

SELECT id, status_id
FROM
contacts AS cc
INNER JOIN
statuses AS s ON (s.id = cc.id)
INNER JOIN (
    SELECT id, max(end_date) as max_end_date FROM statuses
    GROUP BY id 
) s2 ON s2.max_end_date = s.end_date AND s2.id = s.id

This works if I have two rows like the following:

id     end_date
 1     2014-06-02
 2     2015-06-02

It should pull out the row with end_date "2015-06-02" - which it does correctly.

However, it does not work if the rows are like this:

id    end_date
 1    2014-06-02
 2    NULL

It should be returning the row where the end date is NULL.

Any help would be appreciated, thanks.

Upvotes: 0

Views: 98

Answers (1)

Jenn
Jenn

Reputation: 795

So the difficulty in what you're asking is you are trying to see if the table has a null value and what the max(end_date) is. These are inherently two very different problems. These problems are normally tackled very differently because what is efficient for finding one is not the same as what is efficient for finding the other.

Methods that I would normally use are essentially off the table because they would add another inner join on the same table. That is not very good for efficiency. Instead, I think this query will do what you need:

SELECT id, status_id
FROM
contacts AS cc
INNER JOIN
statuses AS s ON (s.id = cc.id)
INNER JOIN (
    SELECT id, max(end_date) as max_end_date, sum(isnull(end_date)) as nullcount
    FROM statuses
    GROUP BY id 
) s2 ON case when nullcount > 0
        Then null
        else s2.max_end_date end = s.end_date
     AND s2.id = s.id

This query is weird, but it will allow you to figure out if there are nulls or not. At the same time that we are finding the max(end_date), this query sums the number of nulls. isnull will return 1 if end_date is null and 0 if it is not null, so if we sum these ints, the result should be the number of nulls the id has for the end_date.

Then, the join condition is a case statement. If the number of nulls is greater than 0, we are going to use null as the end_date value. Otherwise, we will use the max value that the subquery returned.

Upvotes: 1

Related Questions