>My program had a bug and when inserted duplicate records. What SQL can I use to delete all the duplicates, but one?
>
>To find the duplicates I would use the sql below, but only want where cnt_text > 1. Then I want to delete all the records but the lowest sequence number (seq).
>
>select key, seq, desc, comp_dt, count(text) as cnt_text from tickler ;
>where desc = "Letter" and comp_dt = date()-1 ;
>order by key, seq ;
>group by key
>
>thanks

CREATE CURSOR tickler (key i,seq i,text C,DESC C(20),comp_dt D)
INSERT INTO tickler VALUES (1,1,'',"Letter" ,DATE()-1)
INSERT INTO tickler VALUES (1,2,'',"Letter" ,DATE()-1)
INSERT INTO tickler VALUES (1,3,'',"Letter" ,DATE()-1)
INSERT INTO tickler VALUES (2,1,'',"Letter" ,DATE()-1)
INSERT INTO tickler VALUES (2,1,'',"Letter" ,DATE()-1)
* ATTENTION FOR duplex Seq
INSERT INTO tickler VALUES (2,3,'',"Letter" ,DATE()-1)
INSERT INTO tickler VALUES (2,3,'',"Letter" ,DATE()-1)
* don't to use DATE(),TIME(),DATETIME(),... function into the SQL commands
_DATE = DATE()-1
DELETE  FROM tickler WHERE seq NOT IN ;
(select MAX(seq) from tickler T where T.key=tickler.key AND desc = "Letter" and comp_dt = m._date;
HAVING COUNT(text)>1)
BROWSE