X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 0B8729DCDB4
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 24 Mar 2006 13:51:18 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 87935-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 24 Mar 2006 13:51:17 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2])
	by postgresql.org (Postfix) with ESMTP id 946A49DCDC1
	for <pgsql-sql@postgresql.org>; Fri, 24 Mar 2006 13:51:15 -0400 (AST)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id 9376630BA0; Fri, 24 Mar 2006 18:51:15 +0100 (MET)
From: Julie Robinson <funkjunk@bellsouth.net>
X-Newsgroups: pgsql.sql
Subject: Re: SQL Query Newbie Help
Date: Fri, 24 Mar 2006 11:50:57 -0600
Organization: Posted via Supernews, http://www.supernews.com
Message-ID: <1228cchdf3qqfe@corp.supernews.com>
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
X-Accept-Language: en-us, en
MIME-Version: 1.0
References: <12287b48nref60f@corp.supernews.com>
In-Reply-To: <12287b48nref60f@corp.supernews.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@supernews.com
Lines: 55
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200603/399
X-Sequence-Number: 24745

This works, but is there a better solution?

select *
from quality_control_reset T
where date = (
    select max(date)
    from quality_control_reset
    where qualitycontrolrange = T.qualitycontrolrange);


Julie Robinson wrote:
> 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   |   date        |   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   |   date        |   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)
> );