Oracle SQL: Get top 3 results per "category"

Question

I have an Oracle-db-table with multiple sportsteams and sports. Now I need to get the 3 teams (Or less if less than 3 teams) with the lowest id for each sport (based on their id).

(In my example Soccer: Barcelona, Real Madrid, PSG / Hockey: NY Islanders, Nashville Predators, Boston Bruins / Basketball: LA Lakers, Boston Celtics, Cleveland Cavaliers / Baseball: NY Yankees)

My table/entries:

Create Table Teams(
teamid NUMBER,
teamname VARCHAR(20),
Sport VARCHAR(20)
);



INSERT INTO Teams (teamid, teamname, Sport) values (1, 'Barcelona', 'Soccer');
INSERT INTO Teams (teamid, teamname, Sport) values (2, 'Real Madrid', 'Soccer');
INSERT INTO Teams (teamid, teamname, Sport) values (3, 'PSG', 'Soccer');
INSERT INTO Teams (teamid, teamname, Sport) values (4, 'Liverpool', 'Soccer');
INSERT INTO Teams (teamid, teamname, Sport) values (5, 'Bayern Munich', 'Soccer');

INSERT INTO Teams (teamid, teamname, Sport) values (6, 'NY Islanders', 'Hockey');
INSERT INTO Teams (teamid, teamname, Sport) values (7, 'Nashville Predators', 'Hockey');
INSERT INTO Teams (teamid, teamname, Sport) values (8, 'Boston Bruins', 'Hockey');
INSERT INTO Teams (teamid, teamname, Sport) values (9, 'Vancouver Canucks', 'Hockey');
INSERT INTO Teams (teamid, teamname, Sport) values (10, 'NY Rangers', 'Hockey');

INSERT INTO Teams (teamid, teamname, Sport) values (11, 'LA Lakers', 'Basketball');
INSERT INTO Teams (teamid, teamname, Sport) values (12, 'Boston Celtics', 'Basketball');
INSERT INTO Teams (teamid, teamname, Sport) values (13, 'Cleveland Cavaliers', 'Basketball');
INSERT INTO Teams (teamid, teamname, Sport) values (14, 'Huston Rockets', 'Basketball');
INSERT INTO Teams (teamid, teamname, Sport) values (15, 'Chicago Bulls', 'Basketball');

INSERT INTO Teams (teamid, teamname, Sport) values (16, 'NY Yankees', 'Baseball'); 

I have already tried this: Get top results for each group (in Oracle) but i still get all the teams.

My Select Statement based on the example:

SELECT t.teamname, t.sport 
FROM teams t
  LEFT OUTER JOIN teams t2 
    ON (t.teamname = t2.teamname AND t.teamid <= t2.teamid) 
GROUP BY t.teamid, t.teamname, t.sport
HAVING COUNT(*) <= 3 
ORDER BY t.sport asc;

Answer 1

One option uses ROW_NUMBER:

SELECT teamid, teamname, Sport
FROM
(
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Sport ORDER BY teamid) rn
    FROM Teams t
) s
WHERE rn <= 3
ORDER BY Sport, teamid;