IN, ANY, SOME subqyery LIMIT replacement

[risoknop]

Here's my current query:

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?

[r937]

which 3 photos?

[risoknop]

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.

[r937]

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

[risoknop]

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.

[risoknop]

Let's say table photos has these columns:

id,
title,
path,
user_id

And table users has these columns:

id
username

[r937]

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

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

[risoknop]

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.

[risoknop]

By the way this:

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

[r937]

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?

[risoknop]

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

[r937]

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

[risoknop]

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.

[risoknop]

Just to let you know, rudy, I have already solved this problem with query like this (thank you stackoverflow.com):

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.

[r937]

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