clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 921289 fiddles created (11981 in the last week).

DECLARE @vt_Source TABLE (Athlete VARCHAR(50) NOT NULL, Event VARCHAR(50) NOT NULL, Mark DECIMAL(10,2) NOT NULL) INSERT INTO @vt_Source(Athlete, Event, Mark) VALUES ('John', '100M', 10.73), ('John', '100M', 10.96), ('Jerry', '100M', 10.98), ('Jerry', '100M', 11.03), ('Jimmy', '100M', 11.15) ;WITH BestPerf AS (SELECT Athlete ,Event ,Mark /* BestPerf_per_Athlete */ ,ROW_NUMBER() OVER(PARTITION BY Event,Athlete ORDER BY Mark) AS RN FROM @vt_Source AS S ) , /* Rank */ R AS ( SELECT Athlete ,Event ,Mark ,DENSE_RANK() OVER(PARTITION BY Event ORDER BY Mark) AS Position FROM BestPerf AS BF WHERE RN = 1 ) SELECT R.Position ,BP.Athlete ,BP.Event ,BP.Mark FROM BestPerf AS BP LEFT JOIN R AS R ON BP.Athlete = R.Athlete AND BP.Event= R.Event AND BP.Mark = R.Mark
Position Athlete Event Mark
1 John 100M 10.73
John 100M 10.96
2 Jerry 100M 10.98
Jerry 100M 11.03
3 Jimmy 100M 11.15
 hidden batch(es)