how to make an SQL Join with inequality but just select TOP 1 rows for every mach of the inequality?

Question

I have this tables:

table A:

id	value
1	20
2	15
3	10

table B:

id	value
1	20
2	14
3	10

I want all the pairs where A.value >= than B.value. But for every comparison in the WHERE condition i just want the first match. In the example:

I got this query:

SELECT * FROM A, B 
WHERE A.date>=B.date;

A_id	A_value	B_id	B_value
1	20	1	20
1	20	2	14
1	20	3	10
2	15	2	14
2	15	3	10
3	10	3	10

but as i said, i just want the first match of every comparison (asume that a_value and b_value are sorted) So i want to delete (actually ignore) these values:

A_id	A_value	B_id	B_value
1	20	2	14
1	20	3	10
2	15	3	10

and obtain:

A_id	A_value	B_id	B_value
1	20	1	20
2	15	2	14
3	10	3	10

I think i can achieve the result grouping by A_id and A_value and calculating MAX(B_value) but i dont know if this is efficient.

something like this

SELECT A.id,A.Value,MAX(B_value) 
FROM A, B 
WHERE A.date>=B.date 
GROUP BY A.id,A.value;

So the question is: Is there a query that can give me the result i need ?

Answer 1

You can use ROW_NUMBER() (available in MySQL 8.x). For example:

select *
from (
  select
    a.id as a_id, a.value as a_value,
    b.id as b_id, b.value as b_value,
    row_number() over(partition by a.id order by b.value desc) as rn
  from a
  join b on a.id = b.id
        and a.value >= b.value
) x
where rn = 1