ORDER BY num_of_players DESC, team.team_name ASC Īdding the GROUP BY gives the correct answer. GROUP BY team.team_name, ason_id, team.team_timestamp - **ADDED** LEFT JOIN player ON (player.team_id = team.team_id) However, a slight modification of oNare's query gives the same results as I obtained with my first query (with sql_mode not set to ONLY_FULL_GROUP_BY).
But, if sql_mode is set to STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY, this query will fail with the message ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT().) with no GROUP columns is illegal if there is no GROUP BY clause | team_name | strength | team_timestamp | GROUP BY t1.team_id, t1.team_name, t1.season_id - **NOTE** - see discussion belowĪnd the result is not quite the one you want, but close. (SELECT team_id, COUNT(team_id) AS num_players But, I got interested and did it myself, but you will get more people to help you if you provide DDL and DML.Īfter creating and loading the tables, I ran the following SQL. Take a look at the tour and also the "how to help us to help you" blog - both at the bottom left of the page. But you should really have given us the DDL and DML. your) original data.īTW, welcome to the forum. I also created the season table (unchanged from the OP's - i.e. I've added some data to the player and team tables to make the answers more general - see bottom of post for all DDL ( CREATE TABLE tab_name.) and DML ( INSERT INTO tab_name VALUES.) used in this answer. | team_name | num_of_players | team_timestamp | I'm currently working on this query but the result is not correct. | 2 | Season 2 | Aniversary Sportfest | 00:25:10 | | season_id | season_name | season_event_name | season_timestamp | | team_id | season_id | team_name | team_timestamp | | player_id | team_id | player_name | player_jersey_number | player_position | player_email | player_contact_number | player_timestamp |
I have 3 tables: Players: mysql> SELECT * FROM players
0 kommentar(er)
