Re: DISTINCT to get distinct *substrings*?

Thanks for the input, I think I get this now. In my case, the query

SELECT DISTINCT ON (substring(attribute from '^http://[^/]*/'))
attribute from pg_atp where attribute like 'http://%'

doesn't get me just the root of the URL, but the whole URL - but only
for the first row for each individual root. While

SELECT DISTINCT substring(attribute from '^http://[^/]*/') from
pg_atp where attribute like 'http://%'

does what I first intended - get a list of all (distinct) root URLs.
Wieder was gelernt. (Learnt something again. :-)

best regards,
Christoph

Am 08.08.2006 um 20:36 schrieb Ben:

> 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(dot)pingel(at)web(dot)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(at)postgresql(dot)org so that
>> your
>> message can get through to the mailing list cleanly
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster