Query Help, Max Record of items in a group

• Dean Reedy

  SSChasing Mays

  Points: 632

  More actions

  January 16, 2007 at 9:12 am

  #115003

  I have the following table.

  EmployeesChildren:

  Employee,ChildName,ChildSex,ChildAge

  Bob,Johnny,Male,11

  Bob,Billy,Male,12

  Bob,Jill,Female,9

  Bob,Karen,Female,13

  Maggie,Teddy,Male,2

  Maggie,Mathew,Male,3

  Maggie,Maria,Female,1

  Maggie,Angie,Female,5

  Now I need the following results(Oldest Male and Female child of each Employee).

  Results Table:

  Employee,ChildName,ChildSex,ChildAge

  Bob,Billy,Male,12

  Bob,Karen,Female,13

  Maggie,Mathew,Male,3

  Maggie,Angie,Female,5

  Please note I know there is only 'Male' and 'Female' sex types in this example, but I really need it to be for any value in that field. i.e. If the value 'UNKNOWN' was in the field it would give me the oldest of that SexType as well.

  Thanks,

  Dean

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 16, 2007 at 9:26 am

  #683522

  Dean

  Something like this?  I haven't tested it.

  John

  SELECT e.Employee, a.ChildName, a.ChildSex, a.Age

  FROM EmployeesChildren e JOIN

  (SELECT Employee, ChildName, ChildSex, Max(ChildAge) as Age

  FROM EmployeesChildren

  GROUP BY Employee, ChildName, ChildSex) a

  ON e.Employee = a.Employee

• Russel Loski

  SSCertifiable

  Points: 7059

  More actions

  January 16, 2007 at 9:31 am

  #683526

  SELECT e.Employee, e.ChildName, e.ChildSex, e.Age

  FROM EmployeesChildren e JOIN

  (SELECT Employee, ChildSex, Max(ChildAge) as Age

  FROM EmployeesChildren

  GROUP BY Employee, ChildSex) a

  ON e.Employee = a.Employee

  and e.ChildSex = a.ChildSex

  and e.Age = a.Age

  Russel Loski, MCSE Business Intelligence, Data Platform

• Dean Reedy

  SSChasing Mays

  Points: 632

  More actions

  January 16, 2007 at 10:47 am

  #683555

  Thank you Russel Loski,

  That worked perfect!!

  thanks,

  Dean