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@web.de> 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
