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