--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
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