使MySQL每个条目只返回一行

Question

I have two tables in my MySQL setup. One with some Materials and one with the respective Weights of them. Every time some Material is updated, I update the Weight, adding another row in the Weights table, to later keep track of the Weight changes (I also included some timestamps and stuff, but I won't be adding those in the example below, for simplicity.)

        Table "Weights"                  Table "Materials"
###############################   ##############################
# ID #  MaterialID  #  Weight #   # Code # Type # Size # Color #
###############################   ##############################
#  1 #      46      #  456.4  #   #  46  #   B  #  13  # Black #   
#  2 #      46      #  453.2  #   #  47  #   D  #  11  # Green #   
#  3 #      47      #  231.1  #   ##############################
#  4 #      47      #  222.0  #
###############################

Now if I execute a query like this below, I can get the Material and it's corresponding Weight:

SELECT Materials.Code,
       Materials.Type,
       Materials.Size,
       Materials.Color,
       Weights.Weight
FROM 
       Materials
INNER JOIN Weights ON Weights.MaterialID = Materials.Code
WHERE Materials.Code = 46
ORDER BY Weights.Code DESC
LIMIT 1

Result (It shows me the last weight to the corresponding Meterial):

#######################################
# Code # Type # Size # Color # Weight #
#  46  #   B  #  13  # Black #  453.2 #
#######################################

So far, so good, but now...

If I omit the LIMIT and the WHERE clause, I get multiple rows (That is quite obvious, but here I'm stuck now):

#######################################
# Code # Type # Size # Color # Weight #
#  47  #   D  #  11  # Green #  231.1 #
#  47  #   D  #  11  # Green #  222.0 #
#  46  #   B  #  13  # Black #  453.2 #
#  46  #   B  #  13  # Black #  456.4 #
#######################################

My plan was to get only the last inserted weight with the corresponding Material. For one Material, one weight row, like so:

#######################################
# Code # Type # Size # Color # Weight #
#  46  #   B  #  13  # Black #  453.2 #
#  47  #   D  #  11  # Green #  222.0 #
#######################################

I already tried putting a MIN() between the Weights.Weight, expecting that it only would pick one, but then it would only return one row, which is the 46, and ignoring all others.

I'm clueless, already searched all over the Internet, found some answers, but I don't understood nothing on how I could adapt those to fit my needs.

Any help is greatly appreciated.

Answer 1

This should work. I added a filter on the ID to select only the ID's that match the max ID for each material.

SELECT  w.ID , m.Code,
   m.Type,
   m.Size,
   m.Color,
   w.Weight
FROM 
   Materials m
INNER JOIN Weights w ON w.MaterialID = m.Code 
AND 
w.ID IN(SELECT MAX(ID) FROM Weights i WHERE i.MaterialID = m.Code )