Filter by one column and then min of another column in PostgreSQL-CodePudding

I have PostgreSQL like so

team goals points
ABC  2     45
ABC  4     25
XYZ  5     32
ABC  1     25
XYZ  6     23
ABC  1     35
ABC  8     35

How do I get the row where the goals are minimum for teams ABC

The expected out put is

team goals points
ABC  1     25
ABC  1     35

I thought it was simple (may be it is) but I am not able to figure it out. I am new to PostgreSQL, so please guide me.

CodePudding user response：

Assuming the table is called: team_stats, you can just query by team to get all rows of the team you need, and then just order by the number of goals (from lowest to highest) and choose the first row

    select * 
    from team_stats 
    where team = 'ABC'
    order by goals asc
    limit 1

This code below is when there are mutiples rows with min goals & all are needed

select * from team_stats 
where team = 'ABC' 
and goals = (select min(goals) from team_stats where team='ABC')

CodePudding user response：

Try distinct on. This limits to one row per entity, and the order by clause helps you determine which entity to pick:

select distinct on (team)
  team, goals, points
from team_goals
where
  team = 'ABC'
order by
  team, goals

The advantage of this is it will continue to work if you remove the team = ABC filter.

This is a more concise version of this, which should also work:

with cte as (
  select
    team, goals, points,
    min (goals) over (partition by team) as min_goals
  from team_goals
)
select
  team, goals, points
from cte
where
  goals = min_goals and
  team = 'ABC'

The CTE approach can result in duplicates when you have multiple records where the min goals is the same. This may or may not be desirable, depending on your use case. row_number could remove duplicates.