Select unique set of record, distinct on, please help!!!

From: worker <tzhai2007(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Select unique set of record, distinct on, please help!!!
Date: 2008-05-16 16:13:09
Message-ID: c39875fb-5d49-4083-9b76-0000902a6067@8g2000hse.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear all,
I am still new to the PostGres DB, I 've been using other DB for
quite a while, so SQL is OK with me. I am running into problem trying
to condense duplicate records in a table into one record. Naturally, I
think about 'DISTINCT ON' in select, but I just couldn't figure out
the proper syntax to use it correctly. So, please help.

My table is as follow:
Column | Type |
Modifiers
-----------------+-------------------------------
+-------------------------------------------------------
tag_id | character varying(15) |
enddate | date |
storetcodei | integer |
gtlt | character(1) |
value | real |
autoid | integer | not null default
nextval('results_autoid_seq'::regclass)

The main issue is that a lot of records have the same 'tag_id' and
'enddate', which is redundant. So I was trying to get rid of all the
duplicates, but just leave one copy in the database, simple you say,
right? NOT!

I used many variants of the following query, but it seems psql is
not doing the right thing, it always gotten rid of all duplicate, not
leaving even one record from all the duplicates, in other db, all I
need is 'unique', so please could someone just give me a SELECT INOT
or CREATE TABLE AS statements that can rid of the duplicate but leave
one copy in the table.

create table results1 as select distinct on (tag_id,enddate) * from
resultsall;

I searched quite a bit about distinct on, I can not understand the
logic behind the combination of distinct on and order by, so please
help with a working statement?
Many thanks,
T.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Fitzpatrick 2008-05-16 16:47:25 Re: Installing debugger
Previous Message Kevin Neufeld 2008-05-16 16:05:23 table creation/modified timestamp