GetMaxRecord_4_zhong_Method.sql

--11111111111111111111111111111111111111111111111111111111
Select t.nendo,t.kojinno,t.kenshinshuruino,t.jushincnt
From KenshinKihon t
        ,(Select s.nendo,s.kojinno,s.kenshinshuruino,Max(s.jushincnt) jushincnt
         From KenshinKihon s
         Group By s.nendo,s.kojinno,s.kenshinshuruino) s
Where t.kojinno=s.kojinno
          And t.nendo=s.nendo
          And t.kenshinshuruino=s.kenshinshuruino
          And t.jushincnt=s.jushincnt
--222222222222222222222222222222222222222222222222222222
Select t.nendo,t.kojinno,t.kenshinshuruino,t.jushincnt,t.jushindate
From kenshinkihon t
Where Exists(
                    Select 1
                    From kenshinkihon s
                    Where       s.nendo=t.nendo
                             And s.kojinno=t.kojinno
                             And s.kenshinshuruino=t.kenshinshuruino
                    Having Max(s.jushincnt)=t.jushincnt)
--33333333333333333333333333333333333333333333333333333
   Select s.nendo,s.kojinno,s.kenshinshuruino,s.jushincnt
   From
        (
         Select s.nendo,s.kojinno,s.kenshinshuruino,s.jushincnt,Max(s.jushincnt) over(Partition By s.nendo,s.kojinno,s.kenshinshuruino) maxjushincnt
         From KenshinKihon s
         ) s
   Where
        s.jushincnt=s.maxjushincnt