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?
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
id into order by to get desired result consistentlyif 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;
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.
DISTINCTselects all rows where all columns have distinct values. In your example, every combination ofidandnameis distinct, you get all rows.