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.

Please look into Max() and Min(). In your case something like Max(I_FE) ... — Nandostyle, Nov 01 '19 at 23:23
There are two records with `aoc_id = 117` and `i_fw = 1.93`. In this situation, which one do you want to pick? — GMB, Nov 01 '19 at 23:32

score 1 · Answer 1 · answered Nov 01 '19 at 23:24

1

You can create a correlated subquery that recovers the highest i_wf for the current aoc_id and use it to filter the join on package, like so:

select
    a.aoc_id,
    a.controller, 
    a.aoc_form_factor, 
    p.i_fw, 
    p.etrack_id, 
    p.eco,
    p.eco_date
from            
    aoc_model a
    inner join package p
        on a.aoc_id = p.aoc_id
        and p.i_wf = (
            select max(p1.i_wf)
            from package p1
            where p1.aoc_id = p.aoc_id
        )
where controller like 'intel%'

For performance, consider an index on package(aoc_id, i_wf).

Note: table aliases make the query easier to read and understand; I modified your query accordingly.

answered Nov 01 '19 at 23:24

GMB

216,147
25
84
135

This would return two rows with ID = 117 ? Right? From the example data OP has given ? – VBoka Nov 01 '19 at 23:30
That helped a lot but it was still leaving duplicates on occasion (like I would still have a duplicate on 117 id and some other ID's randomly. The answer below from VBoksic was spot on – jbegic Nov 01 '19 at 23:34
Happy to help... If the answer below helped please do mark it as accepted and up vote would be great also :) Thanks! – VBoka Nov 01 '19 at 23:36

VBoka · Accepted Answer · 2019-11-02T01:50:44.190

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

edited Nov 02 '19 at 01:50

answered Nov 01 '19 at 23:24

VBoka

8,995
3
16
24

Awesome... Thank you very much. I was playing with GROUP BY but not at the correct syntax – jbegic Nov 01 '19 at 23:36
You are welcome. Happy to help. Be aware that this will also give you max value from columns: ETRACK_ID, ECO and ECO_DATE – VBoka Nov 01 '19 at 23:37
@jbegic . . . I don't see how this does what you want. The four columns from `PACKAGE` could all come from different rows, not the row with the maximum `I_FW` value. – Gordon Linoff Nov 02 '19 at 01:34
@jbegic , Gordon Linoff is correct. As I have commented when posting the answer : `Be aware that this will also give you max value from columns: ETRACK_ID, ECO and ECO_DATE`. So, you might not get the values from the row where the max I_FW value comes from. I have added a DEMO with few simple and fake rows of data so you can check the result of this query. Cheers! – VBoka Nov 02 '19 at 01:58

score 1 · Answer 3 · answered Nov 02 '19 at 01:34

I am pretty sure this is what you want:

SELECT m.AOC_ID, m.CONTROLLER,  m.AOC_FORM_FACTOR, 
       p.I_FW, p.ETRACK_ID, p.ECO, p.ECO_DATE
FROM AOC_MODEL m JOIN
     (SELECT p.*,
             ROW_NUMBER() OVER (PARTITION BY AOC_ID ORDER BY I_FW DESC) as seqnum
      FROM PACKAGE p
     ) p
     ON m.AOC_ID = p.AOC_ID
WHERE aoc.CONTROLLER LIKE 'intel%' AND p.seqnum = 1;

All the values from PACKAGE in the result set come from the set row.

Get The Most Recent Record

3 Answers3