For me given the sample data the following worked. The inner select says find all first messages, the outer says give me all messages that are thus not first messages.
select * from table1 where messageID NOT IN ( select messageID from table1 group by userID ) Some times just playing with the data will result in an aha moment. I'm assuming where you show row 7 you meant row 8? On Wed, Oct 19, 2011 at 12:43 PM, Derek Downey <de...@orange-pants.com>wrote: > You could do a GROUP_CONCAT to get you close: > > SELECT userID, SUBSTRING_INDEX(GROUP_CONCAT(messageID), ',', 1) messageList > FROM table > GROUP BY userID > > | userID | messageList | > |----------|-------------------| > | 71 | 984 | > | 73 | 441, 489 | > | 74 | 483, 723 | > > Or some such. Note I haven't tested the actual query. It's just a general > direction. > > - Derek Downey > > On Oct 19, 2011, at 1:32 PM, Michael Dykman wrote: > > > I'm afraid that what you are looking for simply cannot be done with MySQL > > alone. You will need to pare your results at the application layer. > > Remember that rows have no inherent order except for conforming to any > > ORDER BY clause contained within the query. > > > > - md > > > > On Wed, Oct 19, 2011 at 1:27 PM, Dotan Cohen <dotanco...@gmail.com> > wrote: > > > >> Assuming a table such this: > >> | ID | messageID | userID | > >> |----|-------------|--------| > >> | 1 | 345 | 71 | > >> | 2 | 984 | 71 | > >> | 3 | 461 | 72 | > >> | 4 | 156 | 73 | > >> | 5 | 441 | 73 | > >> | 6 | 489 | 73 | > >> | 7 | 483 | 74 | > >> | 8 | 523 | 74 | > >> | 9 | 723 | 74 | > >> > >> I need the second, third, fourth, etc messageID for each userID. So I > >> would get a results table such as: > >> | ID | messageID | userID | > >> |----|-------------|--------| > >> | 2 | 984 | 71 | > >> | 5 | 441 | 73 | > >> | 6 | 489 | 73 | > >> | 7 | 483 | 74 | > >> | 9 | 723 | 74 | > >> > >> I've tried playing with count and group by and limit, but I've not > >> found a solution. I can easily get all the rows and then remove the > >> rows that I don't need in PHP, but I'd still like to know if an > >> all-MySQL solution is possible. > >> > >> Thanks! > >> > >> -- > >> Dotan Cohen > >> > >> http://gibberish.co.il > >> http://what-is-what.com > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com > >> > >> > > > > > > -- > > - michael dykman > > - mdyk...@gmail.com > > > > May the Source be with you. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=bdao...@lemonfree.com > >
