CODEWITHSUNDEEP
mysqlsql
Martin
Martin

Reputation: 795

Advanced (probably not?) MYSQL query select statement

Please see the below sample data:

'messages' table data:
id, date, user, seen
674, 1399430687, 2, 0
675, 1399430957, 2, 1399431766 
676, 1399431065, 1, 1399431766
677, 1399431723, 2, 1399431766   
678, 1399434322, 2, 0
679, 1399434330, 2, 0

I want to do a count of only the most recent non seen (seen=0) records to a particular user (user: 2) UP and ONLY UNTIL the next seen record (seen>0). So in the above case there are 3 unseen (seen=0) records to user (user: 2) but only 2 of them I am interested as the next 1 has some seen records in between it. So in the above scenario count of 2 would be returned (not the actual records).

Also if the most recent record to user (user: 2) is seen (seen>0) then count of 0 should be returned. I just can't get my head around how to do this in mysql, or how to really describe it.

This is all I can work out, but in the above scenario this will result in 3, not 2:

 SELECT COUNT( * ) 
 FROM  `messages` 
 WHERE seen = 0
 AND user = 2
 ORDER BY DATE DESC

I hope this makes sense.

Thanks.

Upvotes: 0

Views: 98

Answers (1)

serakfalcon
serakfalcon

Reputation: 3531

You need to find the latest non-zero seen data, and then filter after that.

SELECT Count(id) FROM data WHERE user = 3 AND seen = 0
AND date > (
    SELECT coalesce(MAX(date),0) FROM data WHERE seen <> 0 AND user = 3
);

here's the fiddle

EDIT: The previous code wouldn't return a correct answer if the user hadn't seen any records yet at all, this code fixes it by returning 0 instead of null.

Upvotes: 1

Related Questions