List:General Discussion« Previous MessageNext Message »
From:Derek Downey Date:October 19 2011 5:43pm
Subject:Re: How to select the id of 2+ records for given user?
View as plain text  
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.

Thread
How to select the id of 2+ records for given user?Dotan Cohen19 Oct
  • Re: How to select the id of 2+ records for given user?Michael Dykman19 Oct
    • Re: How to select the id of 2+ records for given user?Derek Downey19 Oct
      • Re: How to select the id of 2+ records for given user?Basil Daoust20 Oct
        • Re: How to select the id of 2+ records for given user?Derek Downey20 Oct
          • Re: How to select the id of 2+ records for given user?Dotan Cohen20 Oct
        • Re: How to select the id of 2+ records for given user?Dotan Cohen20 Oct
        • Re: How to select the id of 2+ records for given user?hsv20 Oct
          • Re: How to select the id of 2+ records for given user?Dotan Cohen21 Oct