How can I "iterate" over a query in a view?

Question

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:

foo	user_id_2	position
42	687	0
40	9832	1
39	12	2
...	...	...

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:

foo	user_id_1	user_id_2	position
42	53	687	0
40	53	9832	1
39	53	12	2
...	...	...	...
193	12	53	0
175	12	9832	1
...	...	...	...

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:

user_id_1	user_id_2	foo
53	687	42
687	53	42
53	9832	40
9832	53	40
53	9832	39
9832	53	39
...	...	...

This would not work with position finding: https://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by What my query does is finding the position that user `53` has in view of all other users. — DarkMath, Oct 16 '21 at 14:37
Please [edit] the question, and share more details (in this question) about where/how you do find this `53`. — Luuk, Oct 16 '21 at 14:38
remove `WHERE us.user_id_1 = 53` and change `AND x.user_id_2 = 53` to `AND x.user_id_2 = us.user_id_1`, does it produce what you need — ProDec, Oct 16 '21 at 14:44
Remove the WHERE clause of the main query and change to: `WHERE x.foo >= us.foo AND x.user_id_2 = us.user_id_1`. This is what your query does only for user 53. By correlating the subquery you expand to all users. — forpas, Oct 16 '21 at 14:44

score 1 · Accepted Answer · answered Oct 16 '21 at 14:49

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.

How can I "iterate" over a query in a view?

1 Answers1