2

So I have a table that looks something like this, where I have user messages store, along with their status (read ('R') or unread ('U')). They are also stored with a status number, to show their sequence in a thread of messages.

messageid  userid   sequence    status
93         250        1          A
93         250        2          U
93         250        3          U
94         250        1          A
95         250        1          U

I would like to count the unread messages in this table for userid# 250. The resulting rows should be:

messageid  userid   sequence    status
93         250        3          U
95         250        1          U

I have the easy part down, but believe I need to inner join with a SELECT(max). Here's the easy part, which works well: 

SELECT messageid FROM message_recips WHERE message_recips.userid=250 AND message_recips.status='N'

Heres what cannot figure out the syntax in adding:

AND message_recips.sequence=(SELECT MAX(message_recips.sequence))

Sincere thanks for any help, it is greatly appreciated! mySQLi or mySQL work fine, as I am in the process of switching over.

Improve this question
5
  • do you want to count the messages or display the last one? Because the sequence number is not reliable if a message can be deleted. Commented Dec 12, 2014 at 4:32
  • count the messages, with the status of "N". Messages can be turned to status "D" for deleted, but only entire threads, not individual messages, so it wouldn't calculate the highest sequence number to be a deleted message. Commented Dec 12, 2014 at 4:35
  • so how is sequence=3 representing the number of unread messages for user 250? According to your data the number of unread messages is 2: the 93 and 95, because they both have an unread part in their sequence. Commented Dec 12, 2014 at 4:37
  • sorry if it is confusing... That row does not represent the number of unread messages, That is just one of the rows I want to be returned from the query. I would like to count the number of rows returned from the query. Commented Dec 12, 2014 at 4:42
  • So the answer you accepted does not answer the question :D But I'm confident sgeddes will know how to fix that. Actually, no. I'll fix that. Commented Dec 12, 2014 at 4:49

2 Answers 2

Reset to default
2

For a given user:

SELECT COUNT(DISTINCT messageid) AS unread_messages
FROM message_recips
WHERE status = 'U'
AND userid = 250;

All users report:

SELECT userid, COUNT(DISTINCT messageid) AS unread_messages
FROM message_recips
WHERE status = 'U'
GROUP BY userid;
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks a lot. Forgot about DISTINCT.
1

If I'm understanding your question correctly, you wish to get the max sequence number where the status is unread grouped by the messageid and userid. You can just use the max aggregate for this:

select messageid, 
       userid,
       max(sequence),
       status
from message_recips
where status = 'U'
group by messageid, userid, status
Improve this answer

8 Comments

I'm not sure it is equivalent
So, it works but it's a coincidence. You cannot just group by like that if it's not a requirement. I believe that this won't work as soon as they decide to add a distinctive column and decide to display all rows for that column.
@Sebas -- can you elaborate, I'm not sure I'm understanding your question? My understanding of the question is to find the last sequence grouped by message and user where status = U. This group by is correct for that.
Hey, thanks for the reply. I cannot see why this wouldn't work given the current columns? Not adding any columns
@sgeddes, no no, you definitely understand it and this works. Thanks. I will choose this as the right answer, just interested in why sebas has to say.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.