CODEWITHSUNDEEP
postgresql
CyprusN
CyprusN

Reputation: 33

PostgreSQL showing people that are born the same month

So let's say I have a table of:

Name        Born
John     1994-01-01
John     1994-02-08
Jack     1995-03-09
Bob      1992-03-10
Tom      1995-07-13
Ronda    1984-01-25

And I want to make it that it only shows 

John 1994-01-01
Ronda 1984-01-25
Jack 1995-03-09
Bob 1992-03-10

Because they are born in the same months.

I've tried different selects with EXTRACT and such but it doesn't seem to work for me:|

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

You can do this with window functions:

select t.*
from (select t.*,
             count(*) over (partition by extract(month from born)) as cnt
      from t
     ) t
where cnt > 1
order by extract(month from born);

Upvotes: 3

Related Questions