SQL select max of a group

Question

The initial table is like this:

Fruit  | Item_ID | Production_line | Amount_produced | Production_date 
---------------------------------------------------------------
Apples | 652     | 1               | 24              | 2016-05-12    
Pears  | 455     | 4               | 54              | 2016-05-16    
Pears  | 455     | 2               | 26              | 2016-05-13    
Apples | 652     | 6               | 65              | 2016-05-14  
Apples | 652     | 3               | 24              | 2016-05-21    
Pears  | 455     | 7               | 54              | 2016-05-17    
Pears  | 455     | 5               | 26              | 2016-05-15    
Apples | 652     | 8               | 65              | 2016-05-22    

What I would like to see as a result is the highest level production line (as they are numbered from 1 up according to the level where they are situated) grouped by Item_ID along with all the other columns:

Fruit  | Item_ID | Production_line | Amount_produced | Production_date 
---------------------------------------------------------------    
Pears  | 455     | 7               | 54              | 2016-05-17   
Apples | 652     | 8               | 65              | 2016-05-22

When I use SELECT with the MAX(Production_line) along with GROUP BY Item_ID at the end of my query to group the fruit according to the Item_ID, I don't get the correct production date (not sure if it pulls the random one or what) nor the correct amount produced.

I do not have a PRIMARY KEY in this table.

I'm working in phpMyAdmin with MySQL.

Solution 1

Have a sub-query that returns each fruit with its highest Production_line value. JOIN with that result:

select f1.*
from fruits f1
join (select fruit, max(Production_line) as maxProduction_line
      from fruits
      group by fruit) f2
    on f1.fruit = f2.fruit and f1.Production_line = f2.maxProduction_line

Solution 2

You may not have an explicit primary key in your table, but I think you want the record with the most recent production date for each Fruit and Item_ID combination. If so, then we can join your table to a subquery which identifies the latest production record for each group.

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT Fruit, Item_ID, MAX(Production_date) AS max_date
    FROM yourTable
    GROUP BY Fruit, Item_ID
) t2
    ON t1.Fruit = t2.Fruit AND
       t1.Item_ID = t2.Item_ID AND
       t1.Production_date = t2.max_date

Solution 3

Use GROUP BY Cluase :

SELECT T1.*
FROM your_table T1
JOIN
(
  SELECT Fruit , Item_ID , MAX(Production_line) Production_line
  FROM your_table 
  GROUP BY Fruit , Item_ID
) A ON T1.Production_line = A.Production_line AND A.Item_ID = T1.Item_ID 

Solution 4

The SELECT MAX(Value) syntax is just to select a particular column you can use this; though am not really sure of what you pasted but this will select the row with the max production_line

SELECT Fruit, Item_Id, max(Production_line) Amount_produced, Production_date FROM YourTable;

OR

SELECT Fruit, Item_Id, Production_line, Amount_produced, Production_date FROM YourTable WHERE Production_line = MAX(Production_line);

View more solutions

Comments

[Tim Biegeleisen]

This is a very basic GROUP BY query...have you tried something yet?

[jarlh]

Can a fruit type have different Item_ID's? If so, add some to the sample data, and adjust the result if needed.

[Tim Biegeleisen]

I didn't see that you posted first +1

[jarlh]

@TimBiegeleisen, thanks. However, I'm not sure if Item_ID has to be the same or not. (Poor sample data...)

[Alen Šimunic]

Item_ID has to be the same for every Fruit. Thanks all, this helped me resolve the problem!