How do you display the max date per category with conditions in postgres-CodePudding

If I have a table like this (lets call it t_info):

surname	date_of_birth	blue_eyes	blonde_hair	right_handed
Smith	2013-06-13	True	True	True
Smith	2014-08-20	True	False	True
Brown	2012-07-27	True	True	False
Brown	2013-01-30	True	False	True
Brown	2014-12-15	True	True	False
Hughes	2014-07-08	True	True	False

I'd quite like a way of knowing the max date of birth for each Surname with a particular condition. If I could return this that would be great:

surname	blue_eyes	blonde_hair	right_handed
Smith	2014-08-20	2013-06-13	2014-08-20
Brown	2014-12-15	2014-12-15	2013-01-30
Hughes	2014-07-08	2014-07-08	Null

I can definitely get each thing separately by doing something like:

select surname, max(date_of_birth) as blue_eyes
from t_info
where blue_eyes
group by surname;

I could do this for each column as subqueries and then join them together on surname, but it seems like there must be a better way using some sort of window function or similar, I just can't think of how it'd work. If the joined subqueries is the right approach then so be it. Please note my actual data isn't this but it is in the same structure in terms of a category, a date and some boolean fields.

I am on version 10.5 of postgres.

CodePudding user response：

SELECT
  surname
, MAX(CASE WHEN blue_eyes THEN date_of_birth END) AS blue_eyes
, MAX(CASE WHEN blonde_hair THEN date_of_birth END) AS blonde_hair
, MAX(CASE WHEN right_handed THEN date_of_birth END) AS right_handed
FROM
  t_info
GROUP BY surname