Get The Most Recent Record

Question

In SQL Server, I am using two tables (AOC_MODEL and PACKAGE that I have joined together. I want to get the most recent version from the I_FW Column for every AOC_ID.

SELECT
    AOC_MODEL.AOC_ID,
    AOC_MODEL.CONTROLLER, 
    AOC_MODEL.AOC_FORM_FACTOR, 
    PACKAGE.I_FW, 
    PACKAGE.ETRACK_ID, 
    PACKAGE.ECO,
    PACKAGE.ECO_DATE
FROM            
    AOC_MODEL 
    INNER JOIN PACKAGE
        ON AOC_MODEL.AOC_ID = PACKAGE.AOC_ID
WHERE CONTROLLER LIKE 'intel%'

I expect to be able to show one record with the highest number from I_FW column for any given AOC_ID.

Like on the attached picture, I would like to see 2 items only where AOC_ID 117 has the highest number of 1.93 comparing to the other 117 items as well as AOC_ID 118 with number 1.20. So in this case, I would like to see ONLY two items instead of 11.

Answer 1

Please try something like this:

SELECT AOC_MODEL.AOC_ID
       , AOC_MODEL.CONTROLLER
       , AOC_MODEL.AOC_FORM_FACTOR
       , MAX(PACKAGE.I_FW)
       , MAX(PACKAGE.ETRACK_ID)
       , MAX(PACKAGE.ECO)
       , MAX(PACKAGE.ECO_DATE)
FROM AOC_MODEL INNER JOIN PACKAGE
ON AOC_MODEL.AOC_ID = PACKAGE.AOC_ID
WHERE CONTROLLER LIKE 'intel%'
GROUP BY AOC_MODEL.AOC_ID
         , AOC_MODEL.CONTROLLER
         , AOC_MODEL.AOC_FORM_FACTOR

Here is the DEMO