Re: SQL Query Newbie Help

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)
> );