SQL - MIN

Question

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!

Answer 1

(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;

Answer 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)

Answer 3

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

Answer 4

Himas one. It was leaner. Thanks anyway Nick. Cheers!

Answer 5

Hmm, doesn't seem like this works either 🤯

Answer 6

Thanks guys! It worked. Didn't know a subquery was required