This question is similar to another one, but I'm providing a simpler example. The other query was too advanced to make sense to me.

Sample (fake) data

Desired outcome

(Goal is having the same id only show up once, and just picking the first match, given an ordering by kg)

QUERY:

SELECT 
    p.id,
    p.gender,
    p.kg
FROM patient p
ORDER BY p.kg
GROUP BY 1

Error:

'p.gender' must be an aggregate expression or appear in GROUP BY clause

And if I change it to GROUP BY 1, 2, I get the same error, one column over:

'p.kg' must be an aggregate expression or appear in GROUP BY clause

How can I solve this?

CodePudding user response：

You can use an aggregate function to get min/max/avg kg. Otherwise you need to replace p.kg with a subquery returning a single version, something like:

SELECT 
    p.id,
    p.gender,
    (select top 1 p.kg from patient where patient.id=p.id)
FROM patient p
ORDER BY p.id

CodePudding user response：

what you need here is a window function first_value combined with distinct, rather than group-by / aggregate.

the window function distinct pattern can sometimes be used to replicate aggregate query

select distinct
  first_value(id) over (partition by gender order by kg) id
, gender
, first_value(kg) over (partitoin by gender order by kg) kg
from patient