How to select MAX(value) in a SELECT

• FreeHansje

  SSChampion

  Points: 11751

  More actions

  January 10, 2006 at 1:46 am

  #89102

  I have this table with the following values:

  Field1 Field2 Field3

  1 200.00 abc

  1 250.00 bcd

  2 100 xyz

  2 110 zyx

  2 105 klm

  I wish to select those records with all the values for Field1, but with the highest value for Field2. In this example the records:

  1 250.00 bcd

  2 110 zyx

  I am sure it must be able to do this in a single select, but I cannot find it. Have googled the internet, but it did not turn up any useful example.

  Help?

  Greetz,
  Hans Brouwer

• Chris Hedgate

  One Orange Chip

  Points: 25041

  More actions

  January 10, 2006 at 2:17 am

  #613742

  SELECT foo.Field1, foo.Field2, foo.Field3

  FROM thetable foo

  INNER JOIN (

  SELECT Field1, MAX(Field2) AS Field2

  FROM thetable

  GROUP BY Field1

  ) bar

  ON foo.field1 = bar.field1 AND foo.field2 = bar.field2

  --
  Chris Hedgate http://www.hedgate.net/
  Contributor to the Best of SQL Server Central volumes
  Articles: http://www.sqlservercentral.com/columnists/chedgate/