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 <derek@stripped>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 <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.
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=1
>>
>>
