Thread: Basic SQL question and kudos

Basic SQL question and kudos

From
Dennis Gearon
Date:
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

Re: Basic SQL question and kudos

From
Craig Ringer
Date:
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

Re: Basic SQL question and kudos

From
Dennis Gearon
Date:
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
>