From: Derek Downey
Date: October 19 2011 10:11pm
Subject: Re: How to select the id of 2+ records for given user?
List-Archive: http://lists.mysql.com/mysql/226121
Message-Id: <4B4AFF24-B5A9-4412-A9F1-0C49998A32F1@orange-pants.com>
MIME-Version: 1.0 (Apple Message framework v1251.1)
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

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.
>=20
> select * from table1 where messageID NOT IN (
> select messageID from table1
> group by userID
> )
>=20
> 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?
>=20
>=20
> On Wed, Oct 19, 2011 at 12:43 PM, Derek Downey =
<derek@stripped>wrote:
>=20
>> You could do a GROUP_CONCAT to get you close:
>>=20
>> SELECT userID, SUBSTRING_INDEX(GROUP_CONCAT(messageID), ',', 1) =
messageList
>> FROM table
>> GROUP BY userID
>>=20
>> | userID | messageList |
>> |----------|-------------------|
>> | 71        | 984                        |
>> | 73        | 441, 489        |
>> | 74        | 483, 723        |
>>=20
>> Or some such. Note I haven't tested the actual query. It's just a =
general
>> direction.
>>=20
>> - Derek Downey
>>=20
>> On Oct 19, 2011, at 1:32 PM, Michael Dykman wrote:
>>=20
>>> 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.
>>>=20
>>> - md
>>>=20
>>> On Wed, Oct 19, 2011 at 1:27 PM, Dotan Cohen <dotancohen@stripped>
>> wrote:
>>>=20
>>>> 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     |
>>>>=20
>>>> 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     |
>>>>=20
>>>> 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.
>>>>=20
>>>> Thanks!
>>>>=20
>>>> --
>>>> Dotan Cohen
>>>>=20
>>>> http://gibberish.co.il
>>>> http://what-is-what.com
>>>>=20
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe:    =
http://lists.mysql.com/mysql?unsub=3Dmdykman@stripped
>>>>=20
>>>>=20
>>>=20
>>>=20
>>> --
>>> - michael dykman
>>> - mdykman@stripped
>>>=20
>>> May the Source be with you.
>>=20
>>=20
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=3Dbdaoust@stripped
>>=20
>>=20