Removing duplicates from a foundset?

Classic Servoy
1

I have a foundset (>2000 records) loaded from loadRecords(sqlString), which includes duplicates.

How do I best remove these duplicates (assuming the foundset is pre-sorted appropriately)?

Thanks,
Neale.

2

Neale:
I have a foundset (>2000 records) loaded from loadRecords(sqlString), which includes duplicates.

How do I best remove these duplicates (assuming the foundset is pre-sorted appropriately)?

Thanks,
Neale.

Why don’t you try to load the records with SELECT DISTINCT: this way, you should end up with only one record per type.

Unless you mean to find a way to delete the duplicate records: in this case this option won’t help.

3

In case of deleting duplicates, use a database tool.
and google on “remove duplicate records SQL”

Here’s one good explanation:

http://www.15seconds.com/issue/011009.htm

4

Thanks for the suggestions so far, but no cigars yet :-(

To clarify: there are no duplicates in the database, I’m refering to duplicate entries in the foundset.

I’ve tried “SELECT DISTINCT table1.pk FROM table1, table2… WHERE … ORDER BY pk”, but that didn’t help.

The source of the problem appears to be that the WHERE implies a join over table1, table2… which returns a resultset with multiple instances of some table1.pk, which are dutifully loaded into the foundset.

I’d normally get around this with a “… GROUP BY table1.pk” - but the rules prohibit “GROUP BY” (there’s other undocumented things you can’t do too, but that’s a separate story).

Any other ideas?

Thanks,
Neale.