How to get MAX record

• RAM-1062518

  SSC Enthusiast

  Points: 146

  More actions

  August 7, 2009 at 12:42 am

  #213229

  I have table named employee and contains columns effective date and incomming time.

  For a particular employee i can have same effective date and different incoming time

  if effective date is same then i have to pick max incoming time and if the effective date

  is not same then i have to select that record also.

  AND effective_date = (select max(effective_date) from NewSwipecard..Individual_CutOffTime CT where CT.emp_id = ICOT.emp_id)

  AND incoming_time = (select max(incoming_time) from NewSwipecard..Individual_CutOffTime CT where CT.emp_id = ICOT.emp_id)

  effective_date IncomingTimeUSER_ID

  2008-10-16 00:00:00.00010:00:00 10732

  2008-10-16 00:00:00.00011:00:00 10732

  2008-10-18 00:00:00.00009:30:00 10780

  2008-11-17 00:00:00.00009:30:00 10851

• Grant Fritchey

  SSC Guru

  Points: 399009

  More actions

  August 7, 2009 at 6:49 am

  #1035945

  How about trying a GROUP BY on the incoming date and a MAX on the effective date?

  SELECT e.IncomingDate

  ,MAX(e.EffectiveDate)

  FROM dbo.Employees e

  GROUP BY e.IncomingDate

  Wouldn't that do it?

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning