I have the the following SQL query.

SELECT
    us.foo,
    us.user_id_2,
    (
        SELECT COUNT(*)
        FROM my_table x
        WHERE x.foo >= us.foo
            AND x.user_id_2 = 53
    ) AS position
FROM my_table us
WHERE us.user_id_1 = 53
ORDER BY position;

This gives me results like this:

This can be interprted as follows: User 687 is the first match for user 53 with score 42. User 9832 is the second match with score 49 and so on. My problem is that the user is 53 is hardcoded in the query. I need the results for every user in the users table. To be more precisely, I want something like this:

What I basically want is iterating over SELECT id FROM users use that id instead my hardcoded 53 from my query.

Ordering does not matter here. I'm using MariadDb 10.3.

How can I do this?

The structure of my_table is:

CodePudding user response：

SELECT
    us.foo,
    us.user_id_1,
    us.user_id_2,
    (
        SELECT COUNT(*)
        FROM my_table x
        WHERE x.foo >= us.foo
            AND x.user_id_2 = us.user_id_1
    ) AS position
FROM my_table us
ORDER BY user_id_1, position;

See db<>fiddle.