SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    IN, ANY, SOME subqyery LIMIT replacement

    Here's my current query:

    Code:
    SELECT p.*, u.username 
    FROM photos AS p 
    INNER JOIN users AS u ON u.id = p.user_id 
    WHERE p.id IN (
        SELECT id  
        FROM photos p
        WHERE user_id  = p.user_id 
        LIMIT 3
    )
    ORDER BY p.id DESC LIMIT 30;
    What this query is supposed to do is: select 30 photos from the "photos" table with a condition that there are at most 3 photos from the same user (or value "id" in the "users" table).

    The problem with this query is that I'm using MySQL 5.1.33 which doesn't yet support LIMIT clause in IN, ANY or SOME subqueries, so the above query doesn't work.

    Is it possible to modify this query to work in MySQL 5.1.33?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    which 3 photos?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    which 3 photos?
    I don't understand the question?

    I want to select 30 photos from the table, but not more than 3 photos from the same user. So maximum will be 3 photos from the same user.

    I'm not sure how to explain it better.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the 3 biggest photos per user? the three earliest photos per user?

    the selection needs to be based on the values of some column in the photos table

    you cannot say "the first 3" because the rows in a table have no sequence
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the 3 biggest photos per user? the three earliest photos per user?

    the selection needs to be based on the values of some column in the photos table

    you cannot say "the first 3" because the rows in a table have no sequence
    You have column "user_id" in the photos table. I want to select rows from the photos table but in such a way that there won't be more than 3 selected rows with the same user_id column value.

  6. #6
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Let's say table photos has these columns:

    Code:
    id,
    title,
    path,
    user_id
    And table users has these columns:

    Code:
    id
    username

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    it has to be some column other than user_id, some column which can distinguish the photo rows belonging to the same user

    here's an example showing the 3 photos with the shortest titles per user
    Code:
    SELECT u.username 
         , p.title
         , p.path
      FROM users AS u
    INNER
      JOIN photos AS p 
        ON p.user_id = u.id 
     WHERE 3 <
           ( SELECT COUNT(*)
               FROM photos
              WHERE user_id = u.id
                AND LENGTH(title) < LENGTH(p.title) )
    ORDER 
        BY p.title DESC LIMIT 30
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well all photos with the same user have the same value of the user_id column. I don't know what other column could be used for that. Maybe the id column in the users table.

  9. #9
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By the way this:

    Code:
    SELECT p.*, u.username 
    FROM photos AS p 
    INNER JOIN users AS u ON u.id = p.user_id 
    WHERE p.id IN (
        SELECT id  
        FROM photos p
        WHERE user_id  = p.user_id 
        LIMIT 3
    )
    ORDER BY p.id DESC LIMIT 30;
    works in newer MySQL versions, so I'm just looking for a query that would do the same thing but that would also work in MySQL 5.1.33

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by risoknop View Post
    I don't know what other column could be used for that.
    as i said, the user_id column cannot distinguish between the various photos for the same user

    you are left to make a choice between id, title, and path

    did you try my query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    as i said, the user_id column cannot distinguish between the various photos for the same user

    you are left to make a choice between id, title, and path

    did you try my query?
    I did try your query and it works but it doesn't do anything close to what I'm trying to achieve so it doesn't help me really.

    The query I posted in the beginning of this thread does what I need but it needs newer version of MySQL to work than I have

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by risoknop View Post
    I did try your query and it works but it doesn't do anything close to what I'm trying to achieve
    doesn't it return 3 photos per user?

    because that's what you ~said~ you needed to achieve
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    doesn't it return 3 photos per user?

    because that's what you ~said~ you needed to achieve
    It doesn't. It returned photos only from 1 user and there are photos from more users in the database.

  14. #14
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just to let you know, rudy, I have already solved this problem with query like this (thank you stackoverflow.com):

    Code:
    SELECT u.username, p1.* FROM photos AS p1
    INNER JOIN users AS u ON u.id=p1.user_id
    LEFT OUTER JOIN photos AS p2 ON p2.user_id=p1.user_id and p2.id <= p1.id
    GROUP BY p1.id
    HAVING COUNT(p2.id) <= 3
    LIMIT 30;
    It's a little bit of tricky solution but does exactly what I needed so I'm fine with it.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    that's the same solution as mine

    except that one compares ids

    mine uses length of title instead, despite much coaxing on my part to try to get you to choose the column to compare on

    but if you use ids, that's cool too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •