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

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

newbie? SQL question

From: <puppet_sock_at_hotmail.com>
Date: 24 Feb 2004 11:57:03 -0800
Message-ID: <c7976c46.0402241157.4f40d299@posting.google.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 Received on Tue Feb 24 2004 - 13:57:03 CST

Original text of this message

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