From: Julie Robinson <funkjunk@bellsouth.net>
Subject: SQL Query Newbie Help
Date: Fri, 24 Mar 2006 10:24:52 -0600
Organization: Posted via Supernews, http://www.supernews.com
Message-ID: <12287b48nref60f@corp.supernews.com>
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 44
To: pgsql-sql@postgresql.org

Given the two tables at the bottom of this email, I'm having trouble 
coming up with a SQL statement that returns all rows in the 
quality_control_reset table where there is only one row for the most 
recent quality_control_range.  Help?

Example:

In table quality_control_reset:

id   |   timestamp   |   qualitycontrolrange
---------------------------------------------
1    |  02/23/2006   |   20
2    |  02/23/2006   |   6
3    |  02/28/2006   |   18
4    |  03/01/2006   |   18
5    |  03/23/2006   |   12
6    |  03/23/2006   |   20

I want the results of the following from the query:

id   |   timestamp   |   qualitycontrolrange
---------------------------------------------
2    |  02/23/2006   |   6
4    |  03/01/2006   |   18
5    |  03/23/2006   |   12
6    |  03/23/2006   |   20


CREATE TABLE quality_control_reset
(
   id int8 NOT NULL,
   date timestamp,
   qualitycontrolrange int8,
   CONSTRAINT quality_control_reset_pkey PRIMARY KEY (id),
   CONSTRAINT fk42a706efb62efa94 FOREIGN KEY (qualitycontrolrange)
       REFERENCES quality_control_range (id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE quality_control_range (
   id int8 NOT NULL,
   code varchar(255),
   CONSTRAINT quality_control_range_pkey PRIMARY KEY (id)
);