0

Alright, so I have a table of persons and each person belongs to a couple. I need to run a query that gets only the older member of each couple. Here is my schema:

id BIGINT
name VARCHAR(32)
couple_id VARCHAR(255)
birthdate TIMESTAMP

And the query I'm working on:

SELECT * FROM person
GROUP BY couple_id
HAVING birthdate > ???

The couple_id is a randomized string. Currently the output looks like this:

1 John        AAA 1985/12/04
2 Jane        AAA 1984/01/02
3 Christopher BBB 1991/07/07
4 Christina   BBB 1992/08/20
5 Alex        CCC 1995/02/07
6 Alexandra   CCC 1996/11/12

I need a query that returns the rows John, Christina, and Alexandra.

NobleUplift
  • 5,631
  • 8
  • 45
  • 87
  • I feel bad for not searching better but I needed this by end-of-day. Thanks for the duplicate link. [This](http://stackoverflow.com/questions/14375099/select-query-return-1-row-from-each-group) is the last post I was looking at. – NobleUplift Oct 27 '15 at 22:02

1 Answers1

1

One way to do it is using a derived table.

SELECT p1.*
FROM person p1
join (select couple_id, max(birthdate) as mxbrthdt
      from person group by couple_id) p2
on p2.couple_id = p1.couple_id and p1.birthdate = p2.mxbrthdt
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58