Query that returns maximum and corresponding FK(ID) group by year which comes from another select that has averages group by FK(ID) and year

I have a raw table which contains the id (pk AI), farm_fk (or HERD), birthdate, nm (some indicator).

I want to get the maximum of nm averages for each existing year and corresponding farm_fk.

What is DID?

1st from table I got the averages of nm for each farm_fk group by year which returns some thing like this I added the inner select query (here I added order by also to ease of reading)

SELECT 
    YEAR(FA_BDATE) AS BYEAR, FARM_FK AS HERD, AVG(NET_MERIT) AS NM
FROM 
    __FARM_ANALYTICS_TBL
GROUP BY 
    FARM_FK, YEAR(FA_BDATE)
ORDER BY
    HERD, BYEAR

Output:

2006    10045   -181.553596496582
2007    10045   -252.123779296875
2008    10045   -97.3785720467567
2009    10045   -46.0490081493671
2010    10045   -2.05634630137476
2011    10045   33.5371181927621
2012    10045   74.3363304953117
2013    10045   124.057859333072
2014    10045   177.423686878542
2015    10045   188.846870697982
2016    10045   241.421725696403
2017    10045   318.593410228532
2018    10045   443.3983160078
2019    10045   483.12452005201
2010    10046   -99.2454333305359
2011    10046   42.3376306125096
2012    10046   71.8295436098769
2013    10046   90.7501822723432
2014    10046   133.500797046962
2015    10046   135.329324710063
2016    10046   223.211583482458
2017    10046   261.208083089193
2018    10046   409.256013000512
2019    10046   468.574509707364
2010    10047   -97.1618871688843
2011    10047   -1.06820678710938
2012    10047   20.5999549464746
2013    10047   5.93872594833374
2014    10047   134.559080774134
2015    10047   221.275759446621
2016    10047   203.30495039622
2017    10047   280.823856965995
2018    10047   304.591577597225
2019    10047   399.748709002112

Secondly, I tried to extract the max of (avg(nm)) for each year and corresponding herd and I tried this

SELECT 
    BYEAR, HERD, MAX(NM) AS MAX_NM
FROM
    (SELECT 
         YEAR(FA_BDATE) AS BYEAR, FARM_FK HERD, AVG(NET_MERIT) AS NM
     FROM 
         __FARM_ANALYTICS_TBL
     GROUP BY 
         FARM_FK, YEAR(FA_BDATE)) AS AVGNM
GROUP BY 
    BYEAR
ORDER BY 
    BYEAR

Which does not work properly since I think according to what I need its wrong to group by HERD and also there are many HERD in previous select for each year. without HERD column in above query I get this which is correct but without corresponding herd id

2005    -258.71630859375
2006    -150.947634379069
2007    -127.1032270704
2008    -5.74109745025635
2009    -19.5938364664714
2010    -2.05634630137476
2011    64.6482777208895
2012    109.018188629743
2013    260.781127929688
2014    219.82367308171
2015    244.199884947291
2016    296.168976219786
2017    391.202879227419
2018    460.009900628413
2019    493.26334651952

Answer

I think you just want to use ROW_NUMBER():

SELECT hy.*
FROM (SELECT YEAR(FA_BDATE) AS BYEAR, FARM_FK AS HERD, AVG(NET_MERIT) AS NM,
             ROW_NUMBER() OVER (PARTITION BY YEAR(FS_BDATE) ORDER BY AVG(NET_MERIT) DESC) as seqnum
      FROM __FARM_ANALYTICS_TBL
      GROUP BY FARM_FK, YEAR(FA_BDATE)
     ) hy
WHERE seqnum = 1
ORDER BY BYEAR;

Note that in the event of ties, this returns just one of the ties. If you want all of them, use RANK() instead of ROW_NUMBER().

Advertisement

Answer