Обсуждение: Basic SQL question and kudos
I need to get a join across about 8 tables to only return rows for the MAX date in one of the tables for all otherwise identicalreturn results. I am trying to get the most recent edit of normalized records that are composed of elements fromall the tables. There could be 1 to 100+ successive edits to the normalized/multi table record. The will be approximately65,000 total records for now, 100,000,000s later. If I make one of the values in the select statement be MAX(the date), does that automatically do grouping on 'the date' orwill I only get ONE row total returned? KUDOS, I almost never need to write the postgres group . . . because it just works. It's only using postgres that I evereven read the digest that I receive. Soon I will have to write more to get performance tips. You guys are great on this list and seem to always have answers,Thx. Dennis Gearon Signature Warning ---------------- EARTH has a Right To Life, otherwise we all die. Read 'Hot, Flat, and Crowded' Laugh at http://www.yert.com/film.php
On 10/06/2010 12:31 AM, Dennis Gearon wrote: > I need to get a join across about 8 tables to only return rows for the MAX date in one of the tables for all otherwiseidentical return results. I am trying to get the most recent edit of normalized records that are composed of elementsfrom all the tables. There could be 1 to 100+ successive edits to the normalized/multi table record. The will beapproximately 65,000 total records for now, 100,000,000s later. > > If I make one of the values in the select statement be MAX(the date), does that automatically do grouping on 'the date'or will I only get ONE row total returned? If I understand what you're proposing correctly, I don't think it'll work. What you need to do is use a subquery to obtain the max of the field of interest, and filter in your WHERE clause so you only return results when the selected value is the maximum for that field. It's hard to be more specific with a fairly general question like this, but if I'm understanding you right this is how I'd start. -- Craig Ringer
Thanks for the start. I'll start on it. Dennis Gearon Signature Warning ---------------- EARTH has a Right To Life, otherwise we all die. Read 'Hot, Flat, and Crowded' Laugh at http://www.yert.com/film.php --- On Tue, 10/5/10, Craig Ringer <craig@postnewspapers.com.au> wrote: > From: Craig Ringer <craig@postnewspapers.com.au> > Subject: Re: [GENERAL] Basic SQL question and kudos > To: "Dennis Gearon" <gearond@sbcglobal.net> > Cc: pgsql-general@postgresql.org > Date: Tuesday, October 5, 2010, 9:13 PM > On 10/06/2010 12:31 AM, Dennis Gearon > wrote: > > I need to get a join across about 8 tables to only > return rows for the MAX date in one of the tables for all > otherwise identical return results. I am trying to get the > most recent edit of normalized records that are composed of > elements from all the tables. There could be 1 to 100+ > successive edits to the normalized/multi table record. The > will be approximately 65,000 total records for now, > 100,000,000s later. > > > > If I make one of the values in the select statement be > MAX(the date), does that automatically do grouping on 'the > date' or will I only get ONE row total returned? > > > If I understand what you're proposing correctly, I don't > think it'll work. > > What you need to do is use a subquery to obtain the max of > the field of interest, and filter in your WHERE clause so > you only return results when the selected value is the > maximum for that field. > > It's hard to be more specific with a fairly general > question like this, but if I'm understanding you right this > is how I'd start. > > -- > Craig Ringer >