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