Ah-hah! :) Actually, I did something similar to that a month or so ago. I ran into a speed limitation on a not-small database (~3mill rows). So be careful.
Luckily in my case, I put all the 'minimum' ids in a memory table with an index and it solved it. It also was a cleanup script, and not something I need to run everyday. -- Derek Downey On Oct 19, 2011, at 6:06 PM, Basil Daoust wrote: > 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 >> >> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
