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
  • 553
  • 6
  • 16
tatulea
  • 113
  • 1
  • 10
  • 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,750
  • 13
  • 40
  • 57
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
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
  • 160,666
  • 10
  • 38
  • 76
  • 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