ORACLE SQL: Correlated subquery for fetching personal details by fastest shortest time athlete in competitive category per year

I realize this might be a variation of fetch the max value per a column, but I am still relatively new to ORACLE SQL and I can't seem to get it working.

I have two tables: Competition times and Participants and I'm trying to do a select query which will print out personal information from participants table such as name and email address if they preformed the best in that specific comp (i.e comp 1) for each year.

Participants (Participant, Name, Email and Phone are all Varchar2)

|PARTICIPANT | NAME | EMAIL   | PHONE |
|P1          | Sam  | x@yahoo | 123.. |
|P2          | Jerry| y@gmail | 456.. |
|P3          | Mike | z@gmail | 789...|
...

Competition_Times (Year is number (4,0), type and participant are varchar2 and time is a date (but DD-MON-YYYY is fixated on 1/Jan/1970 so only hours and minutes matter)

|YEAR        | TYPE      | PARTICIPANT | TIME |
|2020        | Comp 1    | P1          | 20:40|
|2020        | Comp 2    | P1          | 24:30|
|2020        | Comp 1    | P2          | 21:40|
|2020        | Comp 1    | P3          | 18:24|
|2019        | Comp 1    | P1          | 25:00|
|2019        | Comp 1    | P3          | 25:39|
|2019        | Comp 2    | P1          | 22:00|
|2019        | Comp 1    | P2          | 22:50|
....

For example: In year 2020 for comp 1, I'm expecting to have to contact the participant with id of P3, and in the year 2019 for comp 1, I'm expecting to contact the participant with id of P2.

YEAR | TYPE  | NAME | EMAIL
2020 | Comp 1| Mike | z@gmail
2019 | Comp 1| Jerry| y@gmail

I realized that this will need an outer query that passes the year of the competition to the inner query, but I can't seem to figure it out even going through multiple replies of questions of a similar nature.

This is the current code I'm working on:

SELECT YEAR, TYPE, NAME, EMAIL
FROM COMPETITION_TIMES, PARTICIPANTS
WHERE
(SELECT * FROM COMPETITION_TIMES WHERE TYPE = 'Comp 1' AND ROWNUM = 1 ORDER BY TIME ASC)

If anyone could point me how I am supposed to utilize correlated sub-queries or point me to a similar question that revolves around something like this would be much appreciated!

1 answer

  • answered 2019-12-15 02:39 Gordon Linoff

    You can get the participant number in various ways. One uses a correlated subquery:

    select ct.*
    from competition_times ct
    where ct.time = (select min(ct2.time)
                     from competition_times ct2
                     where ct2.year = ct.year and
                           ct2.type = ct.type
                    );
    

    You can use a join or similar mechanism to get the rest of the columns about the participant.