SQLTeam.com | Weblogs | Forums

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

Also which part can I use @ parameters to filter by Gender and EventName?

Here is the actual results from my tables Can it be ordered by mark in ascending order? So that Deron 11.02000 is immediately before rico 11.07?

  • 1 7702 112 1 Deron Phillip 100M Grenville Secondary School 10.7399997711182 0.3 Grenada
  • 7702 94 1 Deron Phillip 100M Ace Track Club 10.9300003051758 -0.6 Grenada
  • 7702 94 1 Deron Phillip 100M Ace Track Club 10.9300003051758 0.5 Grenada
  • 7702 112 1 Deron Phillip 100M Grenville Secondary School 11.0200004577637 -2.4 Grenada
  • 2 7597 115 1 Rico Moultrie 100M Westmorland Senior School 10.7600002288818 2.0 Grenada
  • 7597 99 1 Rico Moultrie 100M Westmorland Secondary School 10.8500003814697 2.4 Grenada
  • 7597 99 1 Rico Moultrie 100M Westmorland Secondary School 11.0799999237061 +0.0 Grenada
  • 7597 115 1 Rico Moultrie 100M Westmorland Senior School 11.1199998855591 -0.4 Grenada
  • 3 7706 97 1 Jahmol Allen 100M Bishop's College 10.8800001144409 2.9 Grenada
  • 7706 97 1 Jahmol Allen 100M Bishop's College 11.1199998855591 -0.6 Grenada
  • 4 7704 95 1 Josh Baptiste 100M Westerhall Secondary School 10.8900003433228 2.9 Grenada
  • 7704 95 1 Josh Baptiste 100M Westerhall Secondary School 11.0200004577637 0.5 Grenada
  • 7704 95 1 Josh Baptiste 100M Westerhall Secondary School 11.0299997329712 -0.6 Grenada
  • 7704 95 1 Josh Baptiste 100M Westerhall Secondary School 11.1499996185303 -2.4 Grenada

Replace this:

 order by a.eventid
         ,b.[rank]
         ,b.avg_mark
         ,a.athleteid
         ,a.mark

with this:

 order by a.eventid
         ,a.mark

You can use a "where" clause ust above the "order by" clause

Thanks again. I commented out a.athleteid in the order by clause and got it to work earlier to so all that I have to do now is to filter out so I only get the event and gender from the parameters submitted.
Thanks bitsmed. I am learning a lot about SQL. Thanks for all the help so far.

When I Place the where clause above the order by clause like this:

   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
		   where EventName=@Event AND Gender=@Gender 			   

order by a.eventid
,a.mark/*
,b.[rank]
,b.avg_mark
,a.athleteid*/

;

The Rankings start from 81

rank athleteid teamid eventid firstname lastname eventname teamname mark wind venue
81 7673 108 1 Jonair Thomas 100M St. David''s Catholic Secondary 12.1099996566772 3.4 Grenada
7673 108 1 Jonair Thomas 100M St. David''s Catholic Secondary 12.1800003051758 4.0 Grenada
84 7724 97 1 Kina Thomas 100M Bishop's College 12.1899995803833 1.4 Grenada
7673 90 1 Jonair Thomas 100M St. David's Track Blazers 12.2299995422363 0.8 Grenada
85 7694 101 1 Shantell Williams 100M Team Athletics St. Vincent 12.2399997711182 1.0 Grenada
86 7677 87 1 Khareena Primus 100M St Joseph convent St. Andrew 12.2600002288818 1.3 Grenada
86 7627 101 1 Ulanda Lewis 100M Team Athletics St. Vincent 12.2600002288818 1.3 Grenada

Replace this code:

       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

with this:

       inner join (select a.athleteid
                         ,a.eventid
                         ,min(a.mark) as mark
                         ,rank() over(partition by a.eventid order by min(a.mark)) as [rank]
                         ,avg(a.mark) as avg_mark
                     from dbo.performance as a
                          inner join dbo.athletes as b
                                  on b.athleteid=a.athleteid
                          inner join dbo.[events] as c
                                  on c.eventid=a.eventid
                    where b.gender=@gender
                      and c.eventname=@event
                    group by a.athleteid
                            ,a.eventid
                  ) as b

and leave out the where clause just above the order by" clause

Ok thanks. It is working. Thanks for all your help. It has been very enlightening for me. I am learning a lot about SQL.

Hi Bitsmed am running into some problems. I am using you code in a stored procedure. It has two parameters @Gender and @Event. When it is run it checks event to determine if the event is a field event. If it is then it has to rank from the biggest mark to the smallest and if its not a field event it has to rank as what we been working on. I tried changing the rank() from min(mark) to max(mark) the ranking for the field event still ranks the smallest mark as first. Here is my stored proc.

CREATE PROC [dbo].[PerformanceByEventAndGender]
@Event varchar(50),
@Gender varchar(10)
AS
BEGIN
IF(@Event='Javelin' or @Event ='Discus' or @Event='Shot Put' or @Event='Hammer' or @Event='Long Jump' or @Event='High Jump' or @Event='Triple Jump' or @Event='Pole Vault')
BEGIN
select case
when a.mark=b.mark
then cast(b.rank as varchar)
else ''
end as [Rank]
/*,a.athleteid
,a.teamid
,a.eventid */
,IIF((a.mark/10 < 1), STR(a.mark, 4,2), STR(a.mark,5,2)) as Mark /a.mark/
,a.wind as Wind
,d.eventname as [Event]
,c.firstname + ' ' + c.lastname as Athlete
,c.BirthDate as [Birth Date]
,e.teamname as [Team]
,a.venue as Venue
,a.PerformanceDate as [Date]

from dbo.performance as a
	inner join (select a.athleteid
						,a.eventid
						,min(a.mark) as mark
						,rank() over(partition by a.eventid order by max(a.mark)) as [rank]
						,avg(a.mark) as avg_mark
					from dbo.performance as a
						inner join dbo.athletes as b
								on b.athleteid=a.athleteid
						inner join dbo.[events] as c
								on c.eventid=a.eventid
					where b.gender=@Gender
					and c.eventname=@Event
					group by a.athleteid
							,a.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
		,a.mark desc
		,b.[rank] /*       
		,b.avg_mark
		,a.athleteid*/
END

ELSE
BEGIN
select case
when a.mark=b.mark
then cast(b.rank as varchar)
else ''
end as [Rank]
/*,a.athleteid
,a.teamid
,a.eventid */
,IIF(FLOOR(a.mark)/60 < 1, RIGHT(CAST(FLOOR(a.mark - (FLOOR(a.mark /60)*60)) AS VARCHAR(2)),2) + '.' + SUBSTRING(CAST('00' + ROUND(a.mark -FLOOR(a.mark),2) AS VARCHAR)+replicate('00',2),3,2),
CAST(CAST(FLOOR(a.mark /60)AS INT) AS VARCHAR) + ':' + RIGHT('00' + CAST(FLOOR(a.mark - (FLOOR(a.mark /60)*60)) AS VARCHAR(2)),2) + '.' + SUBSTRING(CAST('00' + ROUND(a.mark -FLOOR(a.mark),2) AS VARCHAR)+replicate('00',2),3,2)) AS Mark
,a.wind as Wind
,d.eventname as [Event]
,c.firstname + ' ' + c.lastname as Athlete
,c.BirthDate as [Birth Date]
,e.teamname as Team
,a.venue as Venue
,a.PerformanceDate as [Date]

from dbo.performance as a
	inner join (select a.athleteid
						,a.eventid
						,min(a.mark) as mark
						,rank() over(partition by a.eventid order by min(a.mark)) as [rank]
						,avg(a.mark) as avg_mark
					from dbo.performance as a
						inner join dbo.athletes as b
								on b.athleteid=a.athleteid
						inner join dbo.[events] as c
								on c.eventid=a.eventid
					where b.gender=@gender
					and c.eventname=@event
					group by a.athleteid
							,a.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
		,a.mark
		,b.[rank]  /*       
		,b.avg_mark
		,a.athleteid*/
END

END

WHat am I doing wrong here?

I think this might work for you:

create proc dbo.PerformanceByEventAndGender
   @Event varchar(50)
  ,@Gender varchar(10)
as
begin
   declare @event_factor int=case
                                when @Event in ('Javelin'
                                               ,'Discus'
                                               ,'Shot Put'
                                               ,'Hammer'
                                               ,'Long Jump'
                                               ,'High Jump'
                                               ,'Triple Jump'
                                               ,'Pole Vault'
                                               )
                                then -1 /* reverse ranking = highest value is best */
                                else 1  /* normal ranking = lowest value is best */
                             end
   ;
   select case
             when a.mark=abs(b.mark)
             then cast(b.rank as varchar)
             else ''
          end as [Rank]
/*
         ,a.athleteid
         ,a.teamid
         ,a.eventid
*/
         ,case
             when @event_factor=-1
             then ltrim(str(a.mark,10,2))
             else format(dateadd(second,floor(a.mark),0),case when a.mark<60 then '' else 'm:' end+'ss')
                 +substring(ltrim((str(cast(a.mark as decimal(12,5))%1,10,2))),2,10)
          end as Mark
         ,a.wind as Wind
         ,d.eventname as [Event]
         ,c.firstname+' '+c.lastname as Athlete 
         ,c.BirthDate as [Birth Date] 
         ,e.teamname as [Team] 
         ,a.venue as Venue
         ,a.PerformanceDate as [Date]
     from dbo.performance as a
          inner join (select a.athleteid
                            ,a.eventid
                            ,min(a.mark*@event_factor) as mark
                            ,rank() over(partition by a.eventid order by min(a.mark*@event_factor)) as [rank]
                            ,avg(a.mark) as avg_mark
                        from dbo.performance as a
                             inner join dbo.athletes as b
                                     on b.athleteid=a.athleteid
                             inner join dbo.[events] as c
                                     on c.eventid=a.eventid
                       where b.gender=@Gender
                         and c.eventname=@Event
                       group by a.athleteid
                               ,a.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
            ,a.mark*@event_factor
            ,b.[rank]
/*
            ,b.avg_mark
            ,a.athleteid
*/
end

Hi Bitsmed its working perfectly. Thanks for assistance.

Boy that was some nested joins. I don't think I will ever figure out what going on in there. Thanks again for all your help bitsmed. It is greatly appreciated.