; 2011/10/19 17:06 -0500, Basil Daoust
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
2011/10/20 Halász Sándor h...@tbbs.net:
Well done--but
Although, it seems, it is everyone s experience that the desired order is the
order that MySQL yields, all guarantee of that is explicitly deny'd (look up
'GROUP BY'). It is better to be safe and to use MIN:
select * from table1
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 |
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
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|
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
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
On Thu, Oct 20, 2011 at 00:06, Basil Daoust bdao...@lemonfree.com 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 (
On Thu, Oct 20, 2011 at 00:11, Derek Downey de...@orange-pants.com wrote:
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
