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.
