From: Rod Taylor <pg@rbt.ca>
To: Yasir Malik <ymalik@cs.stevens.edu>
Cc: PostgreSQL <pgsql-sql@postgresql.org>
In-Reply-To: 
 <Pine.NEB.4.64.0606011409360.758@pink-elephant.cs.stevens-tech.edu>
References: <df01c91b0606011043g61328d6habaee8fa037a5ccf@mail.gmail.com>
	<Pine.NEB.4.64.0606011409360.758@pink-elephant.cs.stevens-tech.edu>
Date: Thu, 01 Jun 2006 16:26:53 -0400
Message-Id: <1149193613.6071.18.camel@home>
Mime-Version: 1.0
Subject: Re: Am I crazy or is this SQL not possible
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Thu, 2006-06-01 at 14:13 -0400, Yasir Malik wrote:
> > What I would like to do is simply get the last date_sent and it's
> > status for every broadcast.  I can't do a GROUP BY because I can't put
> > an aggregate on the status column.
> >
> > SELECT MAX(date_sent), status
> > FROM broadcast_history
> > GROUP BY broadcast_id
> >
> You could try the following:
> select status
>    from broadcast_history bh
>   where bh.date_sent =
>         (select max(bh2.date_sent)
>            from broadcast_history bh2);
> 
> This reminds me of an interview question:  I was asked how to get a 
> maximum column from a table without using max.  How would you do that?

Find the list of everything that isn't the highest value, then invert
it.

Don't expect it to perform very well though.

        select col
          from foo
         where col not in 
           (select f1.col
              from foo as f1
                join foo as f2 on (f1.col < f2.col)
           );
        

--