Select rows after row with a specific value

Question

Hi, I still get answers to queries and stuff, so please excuse all of sql's frequent questions 8)

Anyways im trying to select rows after a certain value. I do not understand? Good as it really looks.

Table: Messages

+------------+-----------+---------+-------+---------------------------------------+----------------+------------------+ | message_id | thread_id | user_id | to_id | body | message_status | uid_sent_deleted | +------------+-----------+---------+-------+---------------------------------------+----------------+------------------+ | 1 | 1 | 1 | 7 | How are you bro? | read | 1 | | 2 | 1 | 7 | 1 | IM good what about you kenny? | read | 0 | | 3 | 1 | 1 | 7 | Same just chilling how is your sister | read | 1 | | 4 | 1 | 7 | 1 | Shes coool u know just doin great | read | 0 | | 7 | 1 | 1 | 7 | Thats nice | read | 1 | | 8 | 1 | 7 | 1 | Yupp | read | 0 | | 9 | 1 | 1 | 7 | hhahaha | read | 1 | | 10 | 1 | 7 | 1 | anyways.... | unread | 0 | +------------+-----------+---------+-------+---------------------------------------+----------------+------------------+ 

If the message identifier is 9, it has a value of "1" in the uid_sent_deleted column. I am trying to select a row after the last row that has a value of "1" in the uid_sent_deleted column.

here is the sql I'm currently working with. Thanks for the help!

 SELECT message_id, thread_id, messages.user_id, to_id, body, message_status, uid_sent_deleted FROM messages INNER JOIN messages_thread ON messages_thread.id = messages.thread_id WHERE thread_id =1 GROUP BY messages.message_id ORDER BY message_id ASC 

Answer 1

 ... WHERE thread_id = 1 AND id > (SELECT MAX(id) FROM messages WHERE uid_sent_deleted = 1) 

Answer 2

 SELECT message_id, thread_id, messages.user_id, to_id, body, message_status, uid_sent_deleted FROM (SELECT thread_id t_id, MAX(message_id) m_id FROM messages WHERE thread_id =1 and uid_sent_deleted = 1 GROUP BY thread_id) n inner join messages on messages.message_id > n.m_id and messages.thread_id = n.t_id INNER JOIN messages_thread ON messages_thread.id = messages.thread_id ORDER BY message_id ASC LIMIT 1 

Answer 3

The subquery may work, you select MAX (message_id) WHERE uid_sent_deleted = 1, and then join ... a little foggy in syntax.