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

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