Also which part can I use @ parameters to filter by Gender and EventName?
How to rank the best of a set of marks by the same person while not ranking the rest for that person
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.