Defindit Docs and Howto Home

This page last modified: Feb 21 2007
How to do a histogram in SQL:
select (trimmed_length/100)*100||'-'||((trimmed_length/100)*100)+99,count(*)
from bio_sequence
where lib_id=101 group by (trimmed_length / 100)
order by (trimmed_length/100) desc;

Outer join versus "not in". The "not in" version is simpler and easier
to understand.  It seems slower. Better database design would
eliminate the need for both queries.
SELECT exp_id,exp_title FROM experimental WHERE
exp_valid_record = 't' AND
exp_submitted_date > '2003-05-01' AND
exp_pk NOT IN
(SELECT exp_fk FROM analysis WHERE anl_valid_record = 't' AND exp_fk IS NOT NULL)
ORDER BY seq_lab,expected_run,exp_submitted_date; 

BTW, you cannot do a "not in" if the list has a NULL. 
Note that the "anl_submitted_date IS NULL" below only serves to
exclude analysis records.  "anl_pk is null" works just as well.

SELECT * FROM experimental LEFT OUTER JOIN analysis ON exp_pk=exp_fk WHERE
exp_valid_record='t' AND 
anl_submitted_date IS NULL AND 
exp_submitted_date > '2003-05-01'
ORDER BY seq_lab,expected_run,exp_submitted_date";

In retrospect, I'd probably use two queries, and use a Perl hash to
come up with the proper records.

# single table data dump with no \connect statements.
pg_dump -uaRt tablename dbname > outputfile