SELECT DISTINCT substring(attribute from '^http://[^/]*/') from pg_atp where attribute like 'http://%'; w/o DISTINCT there should be duplicates (if any) don't use "DISTINCT ON" at all, it's evil :-) (why? http://chernowiki.ru/index.php?node=38#A13) On 8/8/06, Christoph Pingel <ch.pingel@xxxxxx> wrote:
Hello to the list, here's an SQL question, I hope it's not off topic. From a list of URLs I want to get only the distinct values of the *web sites* these URLs belong to, that is everything before and including the 3rd slash, and I think this should be possible within the DB. I would like to say something like SELECT substring(attribute from '^http://[^/]*/') from pg_atp where attribute like 'http://%' (which works) but get only the distinct values. SELECT DISTINCT ON substring.. doesn't work. Probably I haven't understood the semantics of the DISTINCT keyword. Can anybody help? thanks in advance Christoph
-- Best regards, Nikolay
