Seems Simple, I cant get it

• kknudson

  SSCarpal Tunnel

  Points: 4986

  More actions

  April 5, 2004 at 1:40 pm

  #152106

  I have a statistics DB with data by Statdate (smalldatetime), ServerName (Char(50)).

  There were some occasions where I ran the capture multiple times during a day.  I want to eliminate everything but the last entry for each server for each day.

  I can't figure out how to do it, at least without using cursors.

  Any ideas ????

  
  KlK

• RoadRunner

  SSC-Addicted

  Points: 409

  More actions

  April 5, 2004 at 1:49 pm

  #501800

  Not sure if this will work:

  delete from table t1

  where Statdate != (select max(Statdate) from table t2 where t1.ServerName = t2.ServerName)

• fhanlon

  SSChampion

  Points: 13131

  More actions

  April 5, 2004 at 2:04 pm

  #501801

  try:

  DELETE from Table1 t

  WHERE  Statdate != (SELECT MAX(Statdate) FROM Table1 t1

   WHERE t.ServerName = t1.ServerName 

   AND CONVERT(VARCHAR(10),t.Statdate ,103)  = CONVERT(VARCHAR(10),t1.Statdate ,103))

  Francis

• kknudson

  SSCarpal Tunnel

  Points: 4986

  More actions

  April 5, 2004 at 2:35 pm

  #501804

  Still testing, but I think fhanlon's trick did it.

  This just goes to show that sometimes you need to NOT overthink the problem.  I was working with 2 subselects joined and then some other things.

  Technically for now I don't want to delete the extras rows, just filter them out. 

  But I think that's the code to do it.

   

  Thanks

  
  KlK