Re: newbie? SQL question

From: bdj <B.D.Jensen_at_gmx.net>
Date: Tue, 24 Feb 2004 21:56:32 +0100
Message-ID: <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
;

/Bjoern

<puppet_sock_at_hotmail.com> skrev i en meddelelse news:c7976c46.0402241157.4f40d299_at_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 - 21:56:32 CET

Original text of this message