Re: Am I crazy or is this SQL not possible - Mailing list pgsql-sql

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

--