- 1

SQL - MIN

Can someone help me with this please? How can I select all the values of the 2 first columns but only the MIN value of the third one? Problem : Football is the most popular sport in the world! Look at the following table named league. ***so the 3 columns titles are «team», «scored_goals» & «conceded_goals*** Write a query to output all teams which conceded the least number of goals in the league ordered by scored goals in descending order. Here's my code : SELECT team, scored_goals, MIN(conceded_goals) AS conceded_goals FROM league ORDER BY scored_goals DESC; THANKS!

30th Aug 2021, 6:54 PM
Raphaël Leblanc
Raphaël Leblanc - avatar
6 ответов
+ 3
(Standard solution) 1. select team,scored_goals,conceded_goals from league where conceded_goals=(select MIN(conceded_goals ) from league) order by scored_goals desc; (hack) 2. select team,scored_goals,conceded_goals from league where conceded_goals=2 order by scored_goals desc;
30th Aug 2021, 9:24 PM
Hima
Hima - avatar
+ 2
Try this, it requires a subquery and only gets the teams with the lowest scores/goals 👍 SELECT team, scored_goals, conceded_goals FROM league l1 WHERE conceded_goals = (SELECT MIN(conceded_goals) FROM league l2 WHERE l1.team = l2.team)
30th Aug 2021, 7:23 PM
Tim
Tim - avatar
+ 2
Raphaël Leblanc So which one's code did you use, I didn't have a database so I'm not sure if my solution is the right one
31st Aug 2021, 6:12 PM
Tim
Tim - avatar
+ 2
Himas one. It was leaner. Thanks anyway Nick. Cheers!
31st Aug 2021, 6:20 PM
Raphaël Leblanc
Raphaël Leblanc - avatar
+ 1
Hmm, doesn't seem like this works either 🤯
30th Aug 2021, 7:33 PM
Tim
Tim - avatar
+ 1
Thanks guys! It worked. Didn't know a subquery was required
31st Aug 2021, 6:08 PM
Raphaël Leblanc
Raphaël Leblanc - avatar