Search Postgresql Archives

Re: DISTINCT to get distinct *substrings*?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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?

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@xxxxxxxxxxxxxx so that your
     message can get through to the mailing list cleanly


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux