Re: newbie? SQL question

"bdj" <B.D.Jensen_at_gmx.net> wrote in message news:<403bba66$0$176$edfadb0f_at_dread11.news.tele.dk>...
> try this
>
> select *
> from X, (select cpny as Gcpny, max(val) as Gmax from X group by cpny) T
> where X.cpny = T.Gcpny
> and X.val = T.Gmax
> ;

Yes thanks, that's equivalent to what I did. But the problem was, the val column was actually a date range not a simple value, and I needed the maximum date before the current working date. And the dates were actually in another table linked by the key. And etc. etc. whine whine. So what happened was, the subquery got messy. It works, but it gets big enough that every time I need to modify it I have to drink about 5 cups of coffee to be alert enough to understand it again. Wound up being about 40 lines of SQL. Oh well, not a record I suppose.

I was hoping there was something like a "correspond" command that could be used with the group by. Something like

select correspond(key),cpny,max(val) from X group by cpny;

and the correspond would pick out the key value that corresponded with the max val value. But it looks like the subquery is the only way to go.

But thanks for the help.
Socks Received on Thu Feb 26 2004 - 00:38:16 CET