0

I heve the following two tables in MySQL.

Items

code value
1 6
2 8

Locations

code min max location
1 5 8 loc1
1 4 9 loc2
2 6 10 loc3

I want to get the location for each code in Items, where there is the biggest difference between min and max. For code=1, there are two locations assigned, loc1 and loc2, but the correct one is loc2 because 9-4 is bigger than 8-5.

The output would be

code value location
1 6 loc2
2 8 loc3
dravit
  • 505
  • 4
  • 14
tatulea
  • 113
  • 1
  • 10
  • 3
    StackOverflow is not a free coding service. You're expected to [try to solve the problem first](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users). Please update your question to show what you have already tried in a [mcve]. For further information, please see [ask], and take the [tour] :) – Barmar Feb 20 '21 at 08:52
  • See https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql for how to get the row with the max value of a column in each group. Then apply one of those solutions to a subquery that gets the difference between max and min. – Barmar Feb 20 '21 at 08:53
  • And clarify the PK on your locations table. – Strawberry Feb 20 '21 at 11:30

2 Answers2

1

You can join with the subquery for max diff

    select t.code, t.value, c.localtion 
    from locations c 
    inner join  (
        select a.code, a.value, max(max-min) max_val
        from items a
        inner join Locations b on a.code = b.code 

        ) t on t.code = c.code and t.max_val =(c.max-c.min)
Strawberry
  • 33,338
  • 13
  • 38
  • 57
ScaisEdge
  • 129,293
  • 10
  • 87
  • 97
1

The simplest way to do it is with a correlated subquery that will return the location:

SELECT i.code, i.value,
       (SELECT l.location 
        FROM Locations l 
        WHERE l.code = i.code 
        ORDER BY l.max - l.min DESC LIMIT 1) location
FROM Items i

Or if you are using MySql 8.0+ with ROW_NUMBER() window function:

SELECT i.code, i.value, l.location
FROM Items i 
LEFT JOIN (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY code ORDER BY max - min DESC) rn 
  FROM Locations  
) l ON l.code = i.code AND l.rn = 1

See the demo.
Results:

code value location
1 6 loc2
2 8 loc3
forpas
  • 145,388
  • 9
  • 31
  • 69
  • I found the first solution that you've proposed few hours ago. I'm using MySQL 5.7 and I cannot use the second version.Thanks! – tatulea Feb 20 '21 at 21:27