Re: Am I crazy or is this SQL not possible

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

--