MySql SELECT with GROUP or SUBQUERY with temporary tables? Then ordered TWICE ? How?

Title of question is my thoughts of the solution to the following problem but of course i do not want to limit your answers to that.

Description of database:

Table: Players

Columns: Player_id (integer primary key indexed), Team_id (integer), Player_name (text), Player_points (integer), Player_status (ENUM 'Ok','NotOk')

I want to have the 5 players with most points for each of these Team_id's (1,2,3).

Rows 1-5 team 1, rows 6-10 team 2, rows 11-15 team 3. Players ordered by points and then by status for each team, in one query (CORRECTION: or more queries if there is no choice).

row 1-5 (as you can see for team_id 1):

• 2,1,Jordan,10000,Ok
• 8,1,Pippen,9000,Ok
• 5,1,Rodman,9000,Ok
• 9,1,Grant,9000,NotOk (as you can see they are sorted by points, and then by status)
• 1,1,Parish,5000,Ok

row 6-10 (for team_id 2):

• 14,2,Ewing,8000,NotOk
• 11,2,Starks,7000,Ok
• 10,2,Oakley,7000,NotOk
• 19,2,Harper,6000,Ok
• 1开发者_Go百科3,2,Smith,5000,Ok

row 11-15 (for team_id 3):

• 29,3,Bird,9000,Ok
• 28,3,Rivas,8000,NotOk
• 21,3,Paxson,7000,Ok
• 20,3,Shaw,7000,NotOk
• 22,3,Lohaus,7000,NotOk

I'm seeking a solution with good performance. Tables will have thousands of data, but Team_id's will be just 3 (NBA teams/players here are just for demo purposes)

---

try

SELECT * FROM
(
SELECT Player_id, Team_id, Player_name, Player_points, Player_status FROM Players WHERE Team_id = 1 ORDER BY Team_id, Player_points DESC, Player_status DESC LIMIT 5
UNION ALL
SELECT Player_id, Team_id, Player_name, Player_points, Player_status FROM Players WHERE Team_id = 2 ORDER BY Team_id, Player_points DESC, Player_status DESC LIMIT 5
UNION ALL
SELECT Player_id, Team_id, Player_name, Player_points, Player_status FROM Players WHERE Team_id = 3 ORDER BY Team_id, Player_points DESC, Player_status DESC LIMIT 5
)
ORDER BY Team_id, Player_points DESC, Player_status DESC

EDIT - as per comment: changed UNION to UNION ALL.

---

This may not be easily optimized, but it should work for any number of teams.

SELECT Player_ID, Team_ID, Player_name, Player_points, Player_status
FROM Players
WHERE (
    SELECT COUNT(*)
    FROM Players AS p
    WHERE p.Team_ID = Players.Team_ID
      AND p.Player_points > Players.Player_points
) <= 5
ORDER BY Team_ID ASC, Player_points DESC, Player_status DESC