SQL - fetch latest row with data in specific column or just latest row if no data in that column

Question

I have a table that records a history of address updates, called transaction. The schema is something like row_id (int, PK), user_id (int), address1 (varchar), transdate (timestamp).

I want to query the table and have a single row returned for a user showing what is the latest row (i.e. greatest timestamp), but if there is data in the address1 column I want the latest row with data. If there is no data in this column then just the latest row.

Example:

row_id    user_id    address1        transdate
1         70005      56 The Street   2010-08-25 09:15
2         70005      NULL            2010-08-25 10:04
3         70005      12 Some Road    2010-08-25 11:17
4         70005      NULL            2010-08-25 12:18

With a query like

SELECT  user_id, address1 
FROM    transaction t 
WHERE   user_id = 70005 
        AND row_id = 
            (SELECT MAX(row_id) 
             FROM transaction ti 
             WHERE ti.user_id = t.user_id)

the returned result would be

user_id    address1
70005      NULL

but what I want is

user_id    address1
70005      12 Some Road

because this is the latest row for that user that has some data.

Hope this makes sense. Does anyone have any suggestions?

I am using MySQL 5.1.49 (community). Thanks.

Answer 1

UPDATED

SELECT  user_id, address1 
FROM    transaction t 
WHERE   user_id = 70005 
    AND row_id = 
        IFNULL(
          (SELECT MAX(row_id) 
         FROM transaction ti 
         WHERE ti.user_id = t.user_id AND address1 IS NOT NULL),

         (SELECT MAX(row_id) 
         FROM transaction ti 
         WHERE ti.user_id = t.user_id ) 
        );

Answer 2

SELECT    user_id, address1 
FROM      transaction
WHERE     user_id = 70005
ORDER BY  ISNULL(address1) ASC, row_id DESC
LIMIT     1

This should prioritize rows with data, while still working when address1 is NULL. I would also use transdate instead of row_id.

Answer 3

Take a look at the accepted answer on this question (not my answer, his was better)

By doing a union of the results with address and then the results where there is no address, you can prioritize and still use a simple subquery join.

Note that you're grabbing the max row_id, not that max date, so you're not going to get the results you expect, even if you do this.

Answer 4

SELECT  user_id, address1
FROM    transaction t  
WHERE   user_id = 70005 
ORDER BY case when address1 is not null then 0 else 1 end, transdate desc
limit 1