Select records distinct in one column in Postgresql database

Question

I got the following records where different people have the same name:

id  name  category_id   birthdate   family_id
1   joe   2             2014-05-01  1
2   jack  3             2013-04-01  2
3   joe   2             1964-05-01  1
4   jack  5             1982-05-01  2
5   emma  1             2014-05-01  1
6   joe   3             2003-07-06  3

Now I need a query which results to the following. I want only each name once per family_id. I need all values of each record afterwards including the id. In case the table gets further rows down the road I need them too. So the result should include all values.

id  name  category_id   birthdate   family_id
1   joe   2             2014-05-01  1
2   jack  3             2013-04-01  2
5   emma  1             2014-05-01  1
6   joe   3             2003-07-06  3

I tried it with several approaches (GROUP BY, DISTINCT, DISTINCT ON etc.) but none was working out for me.

When I use a GROUP BY clause (GROUP BY name) I get a ERROR: column "deals.id" must appear in the GROUP BY clause or be used in an aggregate function. But when I put id inside the clause I get all records back.

Same with distinct. There I have to choose all fields on which the result set should be distinct. But I need all values of the record. And because of the primary each record is distinct when i include the id.

I tried it with a sub clause which has filtered all distinct names. I used them in a where clause. But I got all values back including (of course) the not distinct name/family_id records.

Has anybody a helping hand for me?

"none was working out for me" is not an acceptable problem description. Show us what you have tried and the errors you get. — user330315
– user330315, Commented Apr 28, 2016 at 14:20
You need to describe how to chose that single name once per family_id. — jarlh
– jarlh, Commented Apr 28, 2016 at 14:20
@a_horse_with_no_name can you tell me how can i put a table in stack overflow? I tried it with markdown, with a html table and with a simple text table (sensefulsolutions.com/2010/10/format-text-as-table.html). None worked. So I thought this is better than nothing. — coderuby
– coderuby, Commented Apr 28, 2016 at 14:23
@coderuby, an easy way is to take a screenshot of what you are working with and insert the image(s) into the question. — James Oravec
– James Oravec, Commented Apr 28, 2016 at 20:24
@VenomFangs that was exactly what I had done first and what a_horse_with_no_name has criticized with his first comment. But thank you for your helping hand anyhow. — coderuby
– coderuby, Commented Apr 28, 2016 at 21:10

James Oravec · Accepted Answer · 2016-04-28 14:26:05Z

1

You might not of specified all of the fields in your group by and if you included id, then that would make the rows unique.

Try something like:

SELECT 
  name, category_id, birthdate, family_id 
FROM family 
GROUP BY 
  name, category_id, birthdate, family_id;

answered Apr 28, 2016 at 14:26

James Oravec

20.5k31 gold badges100 silver badges164 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jarlh Over a year ago

The same as SELECT DISTINCT, will not solve OP's problem. (All birthdates are unique.)

James Oravec Over a year ago

@a_horse_with_no_name, which id would he like then? Cause it might vary each time the query is run, depending on how the DBE evaluates things.

coderuby · Accepted Answer · 2016-04-28 16:19:24Z

1

It worked with DISTINCT ON.

The following worked quite well:

SELECT DISTINCT ON (table.name, table.family_id) table.* FROM table;

The only thing I have to check is the ordering. But I wanted to share my solution so far.

answered Apr 28, 2016 at 16:19

coderuby

1,2281 gold badge14 silver badges28 bronze badges

Collectives™ on Stack Overflow

Select records distinct in one column in Postgresql database

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related