How to rank the best of a set of marks by the same person while not ranking the rest for that person

Annunaki · May 27, 2017, 3:22am

Hi, I am trying to rank a Table called Performance that contains Athletes, Events, marks.
eg:
Athlete Event Mark
John 100M 10.73
John 100M 10.96
Jerry 100M 10.98
Jerry 100M 11.03
Jimmy 100M 11.15

I would like to rank this using SQL in SQL server to achieve the only the best performance of of each athlete is ranked but the rest of performances are list for eg:

Rank 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

How can this be done where all performances are listed but only the best of each athlete is ranked/number.

stepson · May 27, 2017, 5:40am

My first idea is :

get best performance from each athlete ( i use row_number = 1)
then rank there records overall , to see each athlete is first, second ...
Final join is to get the desired output by joining these sets.( ps: final join is done on all fields, but would be preferable on a primary key / identity but )

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

output for this:

Position	Athlete	Event	Mark
1		    John	100M	10.73
null		John	100M	10.96
2		    Jerry	100M	10.98
null		Jerry	100M	11.03
3		    Jimmy	100M	11.15

http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=c96ef42348afecf7dca36aee72b6e69c

stepson · May 27, 2017, 6:01am

Same output , different approach :

Mark best position of each athlete with 1 else put 0

```
Sum records that are in front
```
```
show rank/position only if it is his best performance (row_number = 1)
```
;WITH BestPerf AS
(SELECT
Athlete
,Event
,Mark
/* BestPerf_per_Athlete */
,CASE WHEN ROW_NUMBER() OVER(PARTITION BY Event,Athlete ORDER BY Mark) = 1 THEN 1 ELSE 0 END AS BP
FROM
@vt_Source AS S
)
, Pos AS
( SELECT
*
,SUM(BP)OVER(PARTITION BY Event ORDER BY Mark ASC) AS Pos
FROM
BestPerf
)
SELECT
CASE WHEN BP = 1 THEN Pos ELSE NULL END as BestPosition
,Athlete
,Event
,Mark
FROM
Pos

bitsmed · May 27, 2017, 9:04am

My suggestion:

select case
          when a.mark=b.mark
          then cast(b.rank as varchar)
          else ''
       end as [rank]
      ,a.athlete
      ,a.[event]
      ,a.mark
  from performance as a
       inner join (select athlete
                         ,[event]
                         ,min(mark) as mark
                         ,rank() over(order by min(mark)) as [rank]
                         ,avg(mark) as avg_mark
                     from performance
                    group by [event]
                            ,athlete
                  ) as b
               on b.[event]=a.[event]
              and b.athlete=a.athlete
 order by a.[event]
         ,b.[rank]
         ,b.avg_mark
         ,a.athlete
         ,a.mark
;

Annunaki · May 27, 2017, 10:54am

Hi Bitsand Thanks for the help thats exactly what I am looking for. I am new to SQL So the code is a little confusing to me. Can you explain the diferent parts more indept so I can get an understanding of what it is doing.

It work perfectly with my table. Thanks again.

Annunaki · May 27, 2017, 10:55am

Hi Stepson Thanks for the help the code is working but I wanted the records that are ranked as null to just be blank in that field. Thanks for the help

bitsmed · May 27, 2017, 11:27am

The sub query will find:

fastest mark foreach athlete in all events (s)he's participated in -> (min(mark) as mark)
rank fastest mark foreach athlete in all events -> rank() over(order by min(mark)) as [rank]
average mark foreach athlete in all events -> for sorting purpose, in case more atheletes rank the same

select athlete
      ,[event]
      ,min(mark) as mark
      ,rank() over(order by min(mark)) as [rank]
      ,avg(mark) as avg_mark
  from performance
 group by [event]
         ,athlete

Result:

athelete event mark  rank avg_mark
John     100M  10.73 1    10.845
Jerry    100M  10.98 2    11.005
Jimmy    100M  11.15 3    11.150

Then join the above results with each performance record, and only show rank if mark is the fastest one the participant performed:

case
   when a.mark=b.mark
   then cast(b.rank as varchar)
   else ''
end as [rank]

Finally sorting so fastest mark is shown first whilst keeping all marks for a given athelete togehter. Also, if more atheletes rank the same, the fastest average of all marks of the athelete, will show first.

Annunaki · May 27, 2017, 11:48am

Ok Thanks a million. That was wery enlightnening. I appreciate the help.

Annunaki · May 29, 2017, 5:25pm

Hi Bitsand I am trying to apply what you showed me to multiple tables but I am have problems getting it to work. I have four Tables Performance, Athletes, Events and Teams.
The structure of my tables are:
CREATE TABLE [dbo].[Athletes] (
[AthleteID] INT IDENTITY (1, 1) NOT NULL,
[FirstName] NVARCHAR (20) NULL,
[LastName] NVARCHAR (20) NULL,
[BirthDate] DATE NULL,
[Gender] NCHAR (10) NULL,
[Email] NVARCHAR (50) NULL,
[Street] NVARCHAR (50) NULL,
[Town] NVARCHAR (15) NULL,
[Parish] NVARCHAR (15) NULL,
[Phone] NVARCHAR (20) NULL,
CONSTRAINT [PK_Athletes] PRIMARY KEY CLUSTERED ([AthleteID] ASC)
);

CREATE TABLE [dbo].[Events] (
[EventID] INT IDENTITY (1, 1) NOT NULL,
[EventName] NVARCHAR (50) NULL,
[GroupID] INT NULL,
[EventCode] NVARCHAR (30) NULL,
CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED ([EventID] ASC),
CONSTRAINT [FK_Events_EventGroups] FOREIGN KEY ([GroupID]) REFERENCES [dbo].[EventGroups] ([GroupID])
);

CREATE TABLE [dbo].[Performance] (
[PerformanceID] INT IDENTITY (1, 1) NOT NULL,
[EventID] INT NULL,
[AthleteID] INT NULL,
[PerformanceDate] DATE NULL,
[TeamID] INT NULL,
[MeetID] INT NULL,
[Mark] FLOAT (53) NULL,
[Venue] NVARCHAR (200) NULL,
[Wind] NCHAR (20) NULL,
CONSTRAINT [PK_Performance] PRIMARY KEY CLUSTERED ([PerformanceID] ASC),
CONSTRAINT [FK_Performance_Athletes] FOREIGN KEY ([AthleteID]) REFERENCES [dbo].[Athletes] ([AthleteID]),
CONSTRAINT [FK_Performance_Events] FOREIGN KEY ([EventID]) REFERENCES [dbo].[Events] ([EventID]),
CONSTRAINT [FK_Performance_Meets] FOREIGN KEY ([MeetID]) REFERENCES [dbo].[Meets] ([MeetID]),
CONSTRAINT [FK_Performance_Teams] FOREIGN KEY ([TeamID]) REFERENCES [dbo].[Teams] ([TeamID])
);

CREATE TABLE [dbo].[Teams] (
[TeamID] INT IDENTITY (1, 1) NOT NULL,
[TeamName] NVARCHAR (50) NULL,
[TeamCode] NVARCHAR (50) NULL,
CONSTRAINT [PK_Teams] PRIMARY KEY CLUSTERED ([TeamID] ASC)
);

I would like to use the AthleteID, TeamID, EventID from the Performance table to select the FirstName, Last Name From Athletes Table, EventName from Events Table, TeamName From Team Table Mark, Wind and venue from the performance table Then Apply the ranking that you did for me but I am having problems with the query. Can you help with that?
The rankings Should list.

The following columns
Ranking, Mark, Wind, Event, Date, Venue

Any Help will be greatly appreciated.

bitsmed · May 29, 2017, 6:31pm

Having no data to test on, makes it inpossible for me to test, but I think this might be close to what you're asking:

select case
          when a.mark=b.mark
          then cast(b.rank as varchar)
          else ''
       end as [rank]
      ,a.athleteid
      ,a.teamid
      ,a.eventid
      ,c.firstname
      ,c.lastname
      ,d.eventname
      ,e.teamname
      ,a.mark
      ,a.wind
      ,a.venue
  from dbo.performance as a
       inner join (select athleteid
                         ,eventid
                         ,min(mark) as mark
                         ,rank() over(order by min(mark)) as [rank]
                         ,avg(mark) as avg_mark
                     from dbo.performance
                    group by athleteid
                            ,eventid
                  ) as b
               on b.eventid=a.eventid
              and b.athleteid=a.athleteid
       inner join dbo.athletes as c
               on c.athleteid=a.athleteid
       inner join dbo.events as d
               on d.eventid=a.eventid
       inner join dbo.teams as e
               on e.teamid=a.teamid
 order by a.eventid
         ,b.[rank]
         ,b.avg_mark
         ,a.athleteid
         ,a.mark
;

Annunaki · May 30, 2017, 2:30am

Hi bitsand its ordering them athletes as they should be but the ranking number assigned for the athlete with the fastest time in the 100 was 561 instead of 1. she had 4 times for the 100 and the other 3 was left as they should be but the the next athlete that was supposed to be ranked 2 was assigned a ranking number of 566 instead of two but everything else was as it should be. I am guessing it rank them on all the events in the database but should rank only on selected events so for 100M it should rank them separate from Long Jump, 200 etc, AM only guess though not very good with SQL. IF you would like me to email you the database so you can have a look at then let me know.

Again thanks for your assistance. I am learning a lot about SQL, Getting there slowly. cheers.

bitsmed · May 30, 2017, 4:22am

Mail is not an option for me. How about Google drive, OneDrive, DropBox or something similar?

Annunaki · May 30, 2017, 4:27am

ok I don't know much about those but if you tell me wha to do I can send it to you

bitsmed · May 30, 2017, 4:32am

Google drive

Annunaki · May 30, 2017, 4:33am

How do I send it that way?
I have never used it before but I have a google email

bitsmed · May 30, 2017, 4:44am

Google drive howto video

Annunaki · May 30, 2017, 4:57am

https://drive.google.com/file/d/0B61jmQJyNSGQQ05YMmF6VF9OVWM/view?usp=sharing
https://drive.google.com/file/d/0B61jmQJyNSGQTmhDODJ3VkVQYjg/view?usp=sharing

Annunaki · May 30, 2017, 12:40pm

Let me know if you can access the files

bitsmed · May 30, 2017, 5:51pm

Change this line:

                         ,rank() over(order by min(mark)) as [rank]

with this:

                         ,rank() over(partition by eventid order by min(mark)) as [rank]

Full query would then be:

select case
          when a.mark=b.mark
          then cast(b.rank as varchar)
          else ''
       end as [rank]
      ,a.athleteid
      ,a.teamid
      ,a.eventid
      ,c.firstname
      ,c.lastname
      ,d.eventname
      ,e.teamname
      ,a.mark
      ,a.wind
      ,a.venue
  from dbo.performance as a
       inner join (select athleteid
                         ,eventid
                         ,min(mark) as mark
                         ,rank() over(partition by eventid order by min(mark)) as [rank]
                         ,avg(mark) as avg_mark
                     from dbo.performance
                    group by athleteid
                            ,eventid
                  ) as b
               on b.eventid=a.eventid
              and b.athleteid=a.athleteid
       inner join dbo.athletes as c
               on c.athleteid=a.athleteid
       inner join dbo.events as d
               on d.eventid=a.eventid
       inner join dbo.teams as e
               on e.teamid=a.teamid
 order by a.eventid
         ,b.[rank]
         ,b.avg_mark
         ,a.athleteid
         ,a.mark
;

Annunaki · May 30, 2017, 6:11pm

Its working Great now. Its Ranking them from 1 and skipping the rest of the performances for that same person. Thanks for all the help. I have a couple of questions. If I want the results to be sorted from the fastest time to the slowest but still keeping the ranking for each person.
for eg Rank Mark Athlete
1 10.73 Deron
10.74 Deron
2 10.76 Rico
10.85 Rico
10.96 Deron
3 10.99 Nathaniel
11.12 Deron
11.19 Rico