newbie? SQL question

[puppet_sock@hotmail.com]

I have a table, called X, that looks like so.

key cpny val (plus other columns not shown)
1 A 1
2 B 3
3 C 4
4 A 7
5 B 9
6 C 2

I want to select the maximum of the val column for each
cpny, but I also want the key for that row.

If I didn't want the key, it would be trivial, it's just

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

And that gives me

A 7
B 9
C 4

But what I want is to include the key column.

4 A 7
5 B 9
3 C 4

Now I've managed to patch together a solution in terms of sub
queries, basically using the first query and then selecting rows
that have those values. Is there an easier way? My actual case
involves dates and a link to another table for the value, and
so on, so it winds up being a half page of gnarly SQL.
Socks

[bdj]

Re: newbie? SQL question

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
;

/Bjoern

<puppet_sock@ho tmail.comskrev i en meddelelse
news:c7976c46.0 402241157.4f40d 299@posting.goo gle.com...

I have a table, called X, that looks like so.
>
key cpny val (plus other columns not shown)
1 A 1
2 B 3
3 C 4
4 A 7
5 B 9
6 C 2
>
I want to select the maximum of the val column for each
cpny, but I also want the key for that row.
>
If I didn't want the key, it would be trivial, it's just
>
select cpny,max(val) from X group by cpny;
>
And that gives me
>
A 7
B 9
C 4
>
But what I want is to include the key column.
>
4 A 7
5 B 9
3 C 4
>
Now I've managed to patch together a solution in terms of sub
queries, basically using the first query and then selecting rows
that have those values. Is there an easier way? My actual case
involves dates and a link to another table for the value, and
so on, so it winds up being a half page of gnarly SQL.
Socks

[puppet_sock@hotmail.com]

Re: newbie? SQL question

"bdj" <B.D.Jensen@gmx .netwrote in message news:<403bba66$ 0$176$edfadb0f@ dread11.news.te le.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