CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
coderuby
coderuby

Reputation: 1218

Select records distinct in one column in Postgresql database

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?

Upvotes: 0

Views: 1298

Answers (2)

coderuby
coderuby

Reputation: 1218

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.

Upvotes: 1

James Oravec
James Oravec

Reputation: 20401

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;

Upvotes: 1

Related Questions