Previous Max Date

• Ekrem

  Valued Member

  Points: 53

  More actions

  December 13, 2006 at 1:12 am

  #176043

  Hi, I want to find previous max date. I'm using a statement like this to get max date but, Could I fetch previous max date by using any parameter? 

   

  select

  max(t.tef_bas_tar) from tef_teftis t where t.kurum_id = &kurum and t.birimler_id = &birim and t.alt_birim1_id = &alt1 and t.alt_birim2_id = &alt2 order by t.tef_bas_tar asc

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  December 13, 2006 at 2:09 am

  #677715

  Are you looking for the second most recent date?  Something like this:

  SELECT MIN(t.tef_bas_tar) FROM

  (SELECT TOP 2 tef_bas_tar FROM tef_teftis

  WHERE kurum_id = @kurum AND birimler_id = @birim AND alt_birim1_id = @alt1 AND alt_birim2_id = @alt2

  ORDER BY tef_bas_tar DESC) t

  John

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  December 13, 2006 at 6:46 am

  #677747

  Will not work if there are duplicate tef_bas_tar values

  You will need to include a GROUP BY

  or you could extract the value to a variable

  DECLARE @maxdate datetime

  SELECT @maxdate = x.tef_bas_tar

  FROM (SELECT TOP 2 t.tef_bas_tar

  FROM tef_teftis t

  WHERE t.kurum_id = @kurum and t.birimler_id = @birim and t.alt_birim1_id = @alt1 and t.alt_birim2_id = @alt2

  GROUP BY t.tef_bas_tar

  ORDER BY t.tef_bas_tar DESC) x

  Far away is close at hand in the images of elsewhere.
  Anon.