There are the following tables:

CREATE TABLE SportTypes( SportTypeID INTEGER NOT NULL PRIMARY KEY, SportTypeName VARCHAR(30)); CREATE TABLE Sportsmans( SportsmanID INTEGER NOT NULL PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, MiddleName VARCHAR(50), LastName VARCHAR(50) NOT NULL, SportTypeID INTEGER NOT NULL, CONSTRAINT fk_sporttype FOREIGN KEY (SportTypeID) REFERENCES SportTypes(SportTypeID)); CREATE TABLE Competitions( CompetitionID INTEGER NOT NULL PRIMARY KEY, CompetitionName VARCHAR(50) NOT NULL, Season VARCHAR(30) NOT NULL, SportTypeID INTEGER NOT NULL, CONSTRAINT fk_sporttype2 FOREIGN KEY (SportTypeID) REFERENCES SportTypes(SportTypeID)); CREATE TABLE Results( SportsmanID INTEGER NOT NULL, CompetitionID INTEGER NOT NULL, Result INTEGER NOT NULL, CONSTRAINT fk_sportsman FOREIGN KEY (SportsmanID) REFERENCES Sportsmans(SportsmanID), CONSTRAINT fk_competition FOREIGN KEY (CompetitionID) REFERENCES Competitions(CompetitionID), CONSTRAINT pk_results PRIMARY KEY (SportsmanID, CompetitionID));

It is necessary to bring out all athletes (all attributes of an athlete) + the best athlete in his sport + the worst athlete in his sport (the best / worst is considered based on the “middle place” in this sport).

The problem arises in my stage when it is necessary to get various athletes according to the result (better and worst), since I cannot figure out how to make independent columns from one table and how to apply to them later.

The request itself is only this:

 select "ID", "sp1First", "sp1Last", "sp1Middle", "sID", min(avgRes) over (partition by SportTypeName) as BestRes from (select sp1.SportsmanID as "ID", sp1.FirstName as "sp1First", sp1.LastName as "sp1Last", sp1.MiddleName as "sp1Middle", sp1.SportTypeID as "sID", SportTypeName, avg(Result) over (partition by sp1.SportsmanID, Competitions.SportTypeID) as avgRes from Sportsmans sp1 inner join Results on sp1.SportsmanID = Results.SportsmanID inner join Competitions on Results.CompetitionID = Competitions.CompetitionID inner join SportTypes on SportTypes.SportTypeID = Competitions.SportTypeID inner join Sportsmans sp2 on sp2.SportsmanID = Results.SportsmanID);
  • one
    How exactly do you need to display the best and worst? A separate line, a sign in a separate column, somehow? And show the request too. - Dmitriy
  • four
    Show that you have already succeeded. You will increase the quality of the question if you create a schema for sqlfiddle , for example. - 0xdb
  • A request that you can already choose at least one (better or worse), or simply considering this very rating will greatly facilitate the task of answering, he will not have to write everything from scratch ... - Mike
  • @Dmitry as I understand it, you need a sign in a separate column, the query added - Ivan
  • And what is meant by " + best athlete ", what information about such an athlete is interesting to you? You can use for example first_value(sp1First) over (partition by SportTypeName order by avgRes) to get the name of the athlete with the lowest average score. And a similar last_value to get the maximum - Mike

2 answers 2

I think the simplest solution is to predict average results.

 ;with cte as ( select r.SportsmanID ,c.SportTypeID ,avg(r.Result) as avgRes Results as r inner join Competitions as c on r.CompetitionID = c.CompetitionID group by c.SportTypeID ,r.SportsmanID ) select "ID" ,"sp1First" ,"sp1Last" ,"sp1Middle" ,"sID" ,Sportsman_min_res ,min_avg_res ,Sportsman_max_res ,max_avg_res from ( select sp1.SportsmanID as "ID" ,sp1.FirstName as "sp1First" ,sp1.LastName as "sp1Last" ,sp1.MiddleName as "sp1Middle" ,sp1.SportTypeID as "sID" ,SportTypeName ,min_res.SportsmanID as Sportsman_min_res ,min_res.avgRes as min_avg_res ,max_res.SportsmanID as Sportsman_max_res ,max_res.avgRes as max_avg_res from Sportsmans as sp1 inner join Results on sp1.SportsmanID = Results.SportsmanID inner join Competitions on Results.CompetitionID = Competitions.CompetitionID inner join SportTypes on SportTypes.SportTypeID = Competitions.SportTypeID outer apply ( select top 1 cte.SportsmanID ,cte.avgRes from cte where cte.SportTypeID = sp1.SportTypeID order by avgRes desc ) as min_res outer apply ( select top 1 cte.SportsmanID ,cte.avgRes from cte where cte.SportTypeID = sp1.SportTypeID order by avgRes asc ) as max_res );

Excuse me if the code does not work, I do not know plsql, I specialize in tsql, in theory it should work.

    Almost correctly, it remains only to compare the maximum and minimum in each sport with the result of each athlete:

     select "ID", "sp1First", "sp1Last", "sp1Middle", "sID", case when min(avgRes) over (partition by SportTypeName) = avgRes then 'Best' when max(avgRes) over (partition by SportTypeName) = avgRes then 'Worse' else null end as BestWorseRes from (select sp1.SportsmanID as "ID", sp1.FirstName as "sp1First", sp1.LastName as "sp1Last", sp1.MiddleName as "sp1Middle", sp1.SportTypeID as "sID", SportTypeName, avg(Result) over (partition by sp1.SportsmanID, Competitions.SportTypeID) as avgRes from Sportsmans sp1 inner join Results on sp1.SportsmanID = Results.SportsmanID inner join Competitions on Results.CompetitionID = Competitions.CompetitionID inner join SportTypes on SportTypes.SportTypeID = Competitions.SportTypeID inner join Sportsmans sp2 on sp2.SportsmanID = Results.SportsmanID);

    And I did not understand it:

    the best / worst is considered based on the “middle place” in this sport

    This refers to the average place of the athlete in each form? If yes, then everything is ok.