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