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=arch...@jab.org
