CODEWITHSUNDEEP
sqlpostgresql
Benjamin Gorman
Benjamin Gorman

Reputation: 370

Best way to get the set of unique fields in PostgreSQL?

For example, say you have a table named person, with the following structure:

    Table "public.person"
      Column       |       Type        |                      Modifiers                      
-------------------+-------------------+-----------------------------------------------------
 id                | integer           | not null default nextval('person_id_seq'::regclass)
 first_name        | character varying | not null
 last_name         | character varying | not null
 birthday          | date              | 
Indexes:
    "person_pkey" PRIMARY KEY, btree (id)

How would you find the unique set of first names?

By 'unique set' I mean this:

   Aaron
   Abbie
   Adam
   ...

Rather than this:

   Aaron
   Aaron
   Abbie
   Abbie
   Abbie
   Adam
   ...

My question is fairly similar to this one, but I feel that question was quite specific to a particular person's use case and also did not get many answers - so I'd like to reopen the topic here.

My intuition would be to use this query:

SELECT DISTINCT ON (first_name) first_name FROM person ORDER BY first_name;

Upvotes: 0

Views: 139

Answers (1)

Jean Jung
Jean Jung

Reputation: 1210

If you want to eliminate duplicate rows do: 

SELECT DISTINCT FIRST_NAME FROM PERSON;

Or if you want to aggregate some fields:

SELECT FIRST_NAME, COUNT(1) CNT FROM PERSON GROUP BY FIRST_NAME;

Upvotes: 2

Related Questions