Обсуждение: SQL Query Newbie Help
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 PRIMARYKEY (id) );
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) > );
On Fri, 24 Mar 2006, Julie Robinson wrote: > 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); If you can use PostgreSQL extensions (and don't care that you might not get two rows if two ids had the same date equaling the max date for a given range), maybe something like: select distinct on (qualitycontrolrange) id, date, qualitycontrolrangefrom quality_control_reset order by qualitycontrolrange,datedesc; Otherwise, you might see how the above compares in plan to something like (not really tested): select T.* from quality_control_reset T inner join(select qualitycontrolrange, max(date) as date from quality_control_resetgroup by qualitycontrolrange) T2on (T.qualitycontrolrange = T2.qualitycontrolrange and T.date=T2.date); > 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) > > );
On Fri, Mar 24, 2006 at 02:29:10PM -0800, Stephan Szabo wrote: > > On Fri, 24 Mar 2006, Julie Robinson wrote: > > > 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); > > If you can use PostgreSQL extensions (and don't care that you might not > get two rows if two ids had the same date equaling the max date for a > given range), maybe something like: > > select distinct on (qualitycontrolrange) id, date, qualitycontrolrange > from quality_control_reset order by qualitycontrolrange,date desc; > > > Otherwise, you might see how the above compares in plan to something like > (not really tested): > > select T.* from quality_control_reset T inner join > (select qualitycontrolrange, max(date) as date from quality_control_reset > group by qualitycontrolrange) T2 > on (T.qualitycontrolrange = T2.qualitycontrolrange and T.date=T2.date); BTW, I believe the new row operator fixes in 8.2 make it possible to use them to do this kind of thing as well... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461