0

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 ?

davidsalmon
  • 13
  • 3
  • What exactly is the (1 specific researched non-duplicate) question? [ask] [Help] PS [mre] – philipxy Jul 28 '22 at 20:52
  • Please in code questions give a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Jul 28 '22 at 21:09

1 Answers1

0

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
The Impaler
  • 45,731
  • 9
  • 39
  • 76