From: | Jean-Luc Lachance <jllachan(at)sympatico(dot)ca> |
---|---|
To: | Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
Cc: | Christoph Haller <ch(at)rodos(dot)fzk(dot)de>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: How to delete the not DISTINCT ON entries |
Date: | 2004-06-16 14:14:14 |
Message-ID: | 40D055B6.5020301@sympatico.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-general pgsql-sql |
If you do not have foreign key restrinctions, create a temp table from
the select as:
CREATE TEMP TABLE tmp AS SELECT DISTINCT ON (location) location, time,
report FROM weatherReports ORDER BY location, time DESC;
TRUNCATE weatherReports; INSERT INTO weatherReports SELECT * FROM tmp;
HTH
Achilleus Mantzios wrote:
> O kyrios Christoph Haller egrapse stis Jun 16, 2004 :
>
>
>>Referring to the DISTINCT ON example
>>
>>SELECT DISTINCT ON (location) location, time, report
>>FROM weatherReports
>>ORDER BY location, time DESC;
>
>
> maybe smth like
>
> delete from weatherReports where (location,time,report) not in
> (SELECT DISTINCT ON (location) location, time, report FROM weatherReports
> ORDER BY location, time DESC)
>
> Note:
> Order by is very important, since it affects which rows are deleted.
>
>
>>How would I delete those entries skipped by the DISTINCT ON expression?
>>
>>TIA
>>
>>Regards, Christoph
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 6: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Gary Mensenares | 2004-06-17 08:11:06 | Hangs on Semop |
Previous Message | Achilleus Mantzios | 2004-06-16 12:18:26 | Re: How to delete the not DISTINCT ON entries |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-06-16 14:32:35 | Re: Multicolumn indexes and ORDER BY |
Previous Message | Jeff | 2004-06-16 14:01:32 | Visual Explain |
From | Date | Subject | |
---|---|---|---|
Next Message | Phil Endecott | 2004-06-16 15:12:27 | Inheritance, plpgsql inserting, and revisions |
Previous Message | Achilleus Mantzios | 2004-06-16 12:18:26 | Re: How to delete the not DISTINCT ON entries |