Re: remove dups
Milos Prudek wrote:
>
> I have a table like:
>
> id text
> 1 aaa
> 1 aab
> 1 aac
> 2 bbb
> 2 bbc
>
> I need to keep only the first record of each id, so that the result is
> like
>
> id text
> 1 aaa
> 2 bbb
>
> When I try to use SELECT FIRST 1... I always get Unknown error 944. I'm
> using IDS (informix dynamic server) 7.31 UC7.
That's an interesting problem. In any event, you should have a way of
ordering the data so that the first record of each group is the one you
want to keep. If you rely on disk order, you may not get the records you
want.
I can see what you are trying with the SELECT FIRST, but you need the
FIRST of each group. So I opted for a stored procedure. Here is my test
case:
------------------------------------------------------------------------
CREATE TABLE dup
(
dup_id INT,
dup_text CHAR(20)
);
INSERT INTO dup VALUES ("1", "aaa");
INSERT INTO dup VALUES ("1", "aab");
INSERT INTO dup VALUES ("1", "aac");
INSERT INTO dup VALUES ("2", "bbb");
INSERT INTO dup VALUES ("2", "bbc");
CREATE PROCEDURE dedup()
RETURNING INT, CHAR(20);
DEFINE i_id LIKE dup.dup_id;
DEFINE o_id LIKE dup.dup_id;
DEFINE o_text LIKE dup.dup_text;
LET o_id = 0;
LET o_text = "";
FOREACH SELECT UNIQUE dup_id
INTO i_id
FROM dup
FOREACH SELECT *
INTO o_id,
o_text
FROM dup
WHERE dup.dup_id = i_id
RETURN o_id, o_text WITH RESUME;
EXIT FOREACH;
END FOREACH;
END FOREACH;
END PROCEDURE;
EXECUTE PROCEDURE dedup();------------------------------------------------------------------------
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /|
| http://www.informix.com http://www.informixhandbook.com |///// / //|
| http://www.iiug.org +-----------------------------------+//// / ///|
| |This email will self-destruct in |/// / ////|
| |10 sec. If you received this email |// / /////|
| |in error, sorry about the mess. |/ ////////|
+----------------------+-----------------------------------+-----------+