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

​x
 
SELECT     YEAR(FA_BDATE) AS BYEAR, FARM_FK AS HERD, AVG(NET_MERIT) AS NMFROM     __FARM_ANALYTICS_TBLGROUP 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

 
2006    10045   -181.5535964965822007    10045   -252.1237792968752008    10045   -97.37857204675672009    10045   -46.04900814936712010    10045   -2.056346301374762011    10045   33.53711819276212012    10045   74.33633049531172013    10045   124.0578593330722014    10045   177.4236868785422015    10045   188.8468706979822016    10045   241.4217256964032017    10045   318.5934102285322018    10045   443.39831600782019    10045   483.124520052012010    10046   -99.24543333053592011    10046   42.33763061250962012    10046   71.82954360987692013    10046   90.75018227234322014    10046   133.5007970469622015    10046   135.3293247100632016    10046   223.2115834824582017    10046   261.2080830891932018    10046   409.2560130005122019    10046   468.5745097073642010    10047   -97.16188716888432011    10047   -1.068206787109382012    10047   20.59995494647462013    10047   5.938725948333742014    10047   134.5590807741342015    10047   221.2757594466212016    10047   203.304950396222017    10047   280.8238569659952018    10047   304.5915775972252019    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

 
SELECT     BYEAR, HERD, MAX(NM) AS MAX_NMFROM    (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 AVGNMGROUP BY     BYEARORDER 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

 
2005    -258.716308593752006    -150.9476343790692007    -127.10322707042008    -5.741097450256352009    -19.59383646647142010    -2.056346301374762011    64.64827772088952012    109.0181886297432013    260.7811279296882014    219.823673081712015    244.1998849472912016    296.1689762197862017    391.2028792274192018    460.0099006284132019    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;

 
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)     ) hyWHERE seqnum = 1ORDER 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