0

I have a table named product which contains two columns:

id  name
1   p1
2   p2
3   p1
4   p3
5   p4

I run the following query:

SELECT DISTINCT id, name FROM product;

As a result, PostgreSQL gives me the following output:

id  name
1   p1
2   p2
3   p1
4   p3
5   p4

I want to avoid duplication of values in the name field, so the desired output should look like this:

1   p1
2   p2
4   p3
5   p4

How should I go about achieving this?

Priidu Neemre
  • 2,813
  • 2
  • 39
  • 40
Shyam Solanki
  • 87
  • 11
  • `DISTINCT` selects all rows where *all* columns have distinct values. In your example, every combination of `id` and `name` is distinct, you get all rows. – Thorsten Dittmar Oct 17 '13 at 09:57

3 Answers3

3

PostgreSQL has a syntax to do this rather efficiently:

select   distinct on (name)
         id,
         name
from     product
order by name,
         id;

The order by name is required.

http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-DISTINCT

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
0

if you want to get non-duplicated name list, use 'distinct name'

select distinct name from product;

and you can also add row number using row_number()

select row_number() over (order by name ) as id, name_list.name
from (select distinct  name from product) as name_list;
Curry
  • 885
  • 5
  • 15
0

I would recommend use the select distinct on() statement above. But if you would like to don't get duplicated data into your table you can add a UNIQUE constraint on your name column.

vinibarr
  • 500
  • 3
  • 5