Re: [SQL] How to delete the not DISTINCT ON entries

[Jean-Luc Lachance]

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])