mysql - SQL - Select Boolean Results from Table -

well ,i didn't find correct title question, sorry that.

i have 1 table store emails sent users.

in table can know if user read or not email.

table structure:

[mailsend_id] (int), [id_user] (int),  [mail_id] (int),  [read] (bit) 

data:

;with cte ( select * (values (1, 10256, 10, 0), (1, 10257, 10, 1), (1, 10258, 10, 1), (1, 10259, 10, 0), (2, 10256, 10, 0), (2, 10257, 10, 0), (2, 10258, 10, 1), (2, 10259, 10, 0), (3, 10256, 10, 1), (3, 10257, 10, 0), (3, 10258, 10, 0), (3, 10259, 10, 0) ) t(mailsend_id, id_user, mail_id, read) 

in example, can see, have 4 users , 3 emails sent.

user 10256 1st email - don't read 2nd email - don't read 3rd email - read 

---

i need make select on table, give [mail_id] , [number], number represent sequential e-mails not read user.

using last example:

give [number] = 3, [mail_id] = 10

return user_id 10259 only.

give [number] = 2, [mail_id] = 10

return user_id 10257, 20259.

give [number] = 1, [mail_id] = 10

return user_id 10257, 10258, 20259.

in words, user_id can have 1 accumulated number of e-mails not read, if user read last e-mail, cant returned in query.

this query today, returns total of emails not read:

select * ( select      a.[user_id],      count(a.[user_id]) tt       emailmkt.mailing_history       a.[mail_id] = 58 ,      a.[read]=0  group      [user_id] ) aa tt > [number] 

so logic not right. want transfer logic sql , not on code, if possible.

sorry if have english errors well.

thanks in advance.

with following query can rolling count of mail read user, based of hypothesis mailsend_id time related (i changed read isread 'cause don't have char ` on keyboard)

select id_user, mail_id      , groupid current      , @roll := case when coalesce(@groupid, '') = groupid                       @roll + 1                       else 1                  end roll      , @groupid := groupid old   (select mh.id_user, mh.mail_id              , concat(mh.id_user, mh.mail_id) groupid         mailing_history mh              inner join (select   id_user                                 , max(case isread                                             when 1 mailsend_id                                             else 0                                        end) lastread                              mailing_history                          group id_user) lr      on mh.id_user = lr.id_user , mh.mailsend_id > lr.lastread order id_user, mailsend_id) 

demo: sqlfiddle

the column roll has rolling count of mail read user.
adding level can check value of roll against number in condition , group_concat user_id