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 <dotancohen@stripped> 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=1
>>
>>
>
>
> --
> - michael dykman
> - mdykman@stripped
>
> May the Source be with you.
