From: B.D.Jensen@gmx.net   
      
   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   
      
    skrev i en meddelelse   
   news: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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)