Re: DISTINCT to get distinct *substrings*?

DISTINCT ON is extremely useful when you know what you're doing. It's
postgres' version of oracle's first_value analytical function, and when
you need it, nothing else really suffices.

On Tue, 8 Aug 2006, Nikolay Samokhvalov wrote:

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>