0

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;
Community
  • 1
  • 1
Benjamin Gorman
  • 360
  • 1
  • 11
  • Can you link to an [SQLFiddle](http://sqlfiddle.com/) that reproduces the problem? When I tried reproducing on my own database the query worked perfectly. – Mureinik Oct 10 '15 at 17:20
  • 1
    You don't need `distinct on()` a plain `distinct` is enough `select distinct first_name from person;` –  Oct 10 '15 at 17:23
  • @Mureinik I've been unable to reproduce the problem in an SQLFiddle. There may be a deeper issue with my database... Running `SELECT * FROM person ORDER BY first_name` returns results for multiple people named Abbie, such as **Abbie Smith**, **Abbie Jones**, however `SELECT * FROM person WHERE first_name='Abbie'` returns just one result for **Abbie Smith**... This may warrant a new question. – Benjamin Gorman Oct 10 '15 at 17:39
  • 2
    Probably trailing spaces in the name. Try `select * from person where trim(first_name) = 'Abbie'` (and consequently `distinct trim(first_name)` –  Oct 10 '15 at 17:53
  • @a_horse_with_no_name this was the problem! Thanks a lot :) – Benjamin Gorman Oct 10 '15 at 18:29

1 Answers1

2

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;
Jean Jung
  • 1,200
  • 12
  • 29