Tuesday 4 September 2018

The MySQL Max statement does not return the result

I have the following MySQL statement

SELECT * FROM user_messages AS T WHERE user_id = '33' AND id = (SELECT Max(id) from user_messages AS TT WHERE T.from_userid = TT.from_userid) ORDER BY status, id DESC

The problem I seem to be having is when I only have one record. I would think that MySQL would return the single record associated with user_link = '33', but instead it returns nothing.
I need to use the "Max" function because I use it to pull the most recent entries. I am trying to avoid having multiple queries or having to use php to sort also. Any help much appreciated!

This is your query:
SELECT *
FROM user_messages AS T
WHERE user_id = '33' AND
      id = (SELECT Max(id)
            from user_messages AS TT
            WHERE T.from_userid = TT.from_userid
           )
ORDER BY status, id DESC

Here are three reasons it could be failing to return any rows. First, user_id = '33' may not exist in the table. Second from_userid may be NULL. Third, the id value may be NULL for all matching records.
Perhaps this simpler version would help:
select *
from user_messages um
where user_id = '33'
order by id desc
limit 1

0 comments:

Post a Comment