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

Поиск
Список
Период
Сортировка
От worker
Тема Select unique set of record, distinct on, please help!!!
Дата
Msg-id c39875fb-5d49-4083-9b76-0000902a6067@8g2000hse.googlegroups.com
обсуждение исходный текст
Ответы Re: Select unique set of record, distinct on, please help!!!
Список 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.


В списке pgsql-general по дате отправления:

Предыдущее
От: Toni Tortosa
Дата:
Сообщение: Pg_dump version mismatch
Следующее
От: "${spencer}"
Дата:
Сообщение: writing a function without installing a language