Don't quite understand how you create the list, but in order to exclude records, I'd typically do

SELECT <whatever>
FROM MyTable M1
WHERE NOT EXISTS(
SELECT * FROM MyTable M2
WHERE M1.CUSTOM_NOTICE_ID = M2.CUSTOM_NOTICE_ID
AND M1.rdb$db_key < M2.rdb$db_key)

If the table has a primary key, I'd use that rather than rdb$db_key.

Alternatively, you can use LEFT JOIN like this:

SELECT <whatever>
FROM MyTable M1
LEFT JOIN MyTable M2
ON M1.CUSTOM_NOTICE_ID = M2.CUSTOM_NOTICE_ID
AND M1.rdb$db_key < M2.rdb$db_key
WHERE M2.CUSTOM_NOTICE_ID IS NULL

(both alternatives should have similar performance, use the one you find most readable)

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of SoftTech
Sent: 2. desember 2010 17:30
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Return a unique key value in a string list

Greetings All,

Firebird 1.53

Inside a stored procedure I need to build a comma delimited string list
based on a custom notice ID and return it as a field on each record.

Let's say there are 500 records in the result set and I only want to place
unique values in the string list like 12,243,50,62,129 but of course there
are many records in the result set that could have the same custom notice
ID.

Here is an example of the first 6 records.
Notice records 1 and 3 are identical and records 5 and 6 have the same
NOTICE_ID but different CUSTOM_NOTICE_ID's

Record 1
NOTICE_ID: 255
CUSTOM_NOTICE_ID: 12

Record 2
NOTICE_ID: 267
CUSTOM_NOTICE_ID: 243

Record 3
NOTICE_ID: 255
CUSTOM_NOTICE_ID: 12

Record 4
NOTICE_ID: 288
CUSTOM_NOTICE_ID: 50

Record 5
NOTICE_ID: 301
CUSTOM_NOTICE_ID: 62

Record 6
NOTICE_ID: 301
CUSTOM_NOTICE_ID: 129

Currently the NOTICE_CUSTOM_ID_LIST field returned for the first result
would contain 12,243,12,50,62,129 and I would prefer 12,243,50,62,129
instead.

Does anyone know of a way to check if the string I'm building already
contains a value so it is not duplicated in the string list?

In other words by the time I process record 3 if '12' already exist in the
string '12,243', exclude it.

Any help truly appreciated,

Thanks,
Mike