Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Re: newbie? SQL question

Re: newbie? SQL question

From: <puppet_sock_at_hotmail.com>
Date: 25 Feb 2004 15:38:16 -0800
Message-ID: <c7976c46.0402251538.2a85191c@posting.google.com>

"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 Wed Feb 25 2004 - 17:38:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US