Search Postgresql Archives

Re: SELECT question

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

 



Am 2007-08-17 12:53:41, schrieb Michael Glaesemann:
> 
> On Aug 17, 2007, at 7:27 , Michelle Konzack wrote:
> 
> >*********************************************************************
> >* Do not Cc: me, because I am on THIS list, if I write here.        *
> 
> You might want to consider changing your mailing list subscription  
> settings to "eliminatecc", e.g., send email to
> majordomo@xxxxxxxxxxxxxx (not the list address!) with body
> 
> set pgsql-general eliminatecc
> 
> This should prevent the mailing list from sending you a second copy.

Which mean, my "INBOX.ML_pgsql.general/" will never receive
messages and break all threads where someone send me CC's...

> I think what you want is something like:
> 
> SELECT DISTINCT ON (website_reference) website_reference,  
> download_date, file_path
> FROM indextable
> WHERE download_date <= ? -- whatever date you're interested in
> ORDER BY website_reference, download_date DESC;
> 
> This should return the most recent website_reference and its  
> download_date that's earlier than the download_date specified in the  
> WHERE clause.
> 
> DISTINCT ON is a (very helpful) PostgreSQL extension. You can get  
> similar results using a subquery;

I have never used "DISTINCT ON" (it was not known to me)
and was trying subqueries...  :-/

> SELECT website_reference, download_date, file_path
> FROM indextable
> NATURAL JOIN (
>     SELECT website_reference, max(download_date) as download_date
>     FROM indextable
>     WHERE download_date <= ?
>     GROUP BY website_reference
>     ) most_recent_versions;
> 
> This may return more than one row per website_reference if the  
> website_reference has more than on file_path for a particular  
> download_date.
> 
> Does this help? If not, could you give a bit more of a concrete example?

I have an Enterprise which do researches  :-)  and I have a local cache
of more then 150.000.000 URL's and its content (~8 TByte)...  (I have
hit over 2000 md5 collisons and now using sha384)  Also I get per day
nearly 100.000 new files...

OK, HTML pages are downloaded and go into the first table like

	indextable	FULL_URL, SHA384SUM

and the second table

	content		SERNUM (uniq), SHA384SUM (pri), LOCAL_PATH

the saved file get as the name the SHA384 name

If I open an HTML-URL with a specific date, it is parsed and the URL's
inline are adapted to make it work from my database, e.g.

    http://www.postgresql.org/index.html

will become

    http://webcache/show.php?date=123456789&url=http://www.postgresql.org/index.html

inline elements and already downloaded other links will bekome

    http://webcache/show.php?date=123456789&url=<original_url>

Thanks to the PostgreSQL developers that they have created "tablespace"
and "table partitioning" since searching in 150.000.000 ROW's is the
hell.  

> (Is is just me or have there been a lot of queries that can be solved  
> using DISTINCT ON recently?)

I do not know...  Since when does "DISTINCT ON" exist?

Greetings
    Michelle Konzack
    Systemadministrator
    Tamay Dogan Network
    Debian GNU/Linux Consultant


-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack   Apt. 917                  ICQ #328449886
                   50, rue de Soultz         MSN LinuxMichi
0033/6/61925193    67100 Strasbourg/France   IRC #Debian (irc.icq.com)

<<attachment: signature.pgp>>


[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