Selecting records

• keeganh

  SSC Enthusiast

  Points: 123

  More actions

  November 12, 2003 at 3:16 am

  #66158

  I have a table like this...

  IDDate

  122/02/2003

  123/02/2003

  119/03/2003

  118/02/2003

  211/02/2003

  212/01/2003

  313/03/2003

  315/02/2003

  319/02/2003

  421/02/2003

  What I want is a result set with each ID and its max date...

  IDDate

  119/03/2003

  211/02/2003

  313/03/2003

  421/02/2003

  I then want to filter the results so that only dates before or after a particular date or between two dates are returned

  eg results where max(date) < 22/02/2003 would return

  IDDate

  211/02/2003

  421/02/2003

  or results where max(date) > 20/02/2003 and < 15/03/2003 would return

  IDDate

  313/03/2003

  421/02/2003

  I've tried various T-SQL statements using group by and having and using sub queries but I can't get the results I need. Any suggestions?

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  November 12, 2003 at 3:29 am

  #481672

  The following should get the 3 results you require:-

  select id,max(Date) from tablename

  group by id

  and

  select id,max(Date) from tablename

  group by id

  having max(Date) < '22/02/2003'

  and

  select id,max(Date) from tablename

  group by id

  having max(Date) between '20/02/2003' and '15/03/2003'

  Beware, specifying a date without a time will default the time to midnight, so if you want all records for a given date you will need to do Between '15/03/2003' and '15/03/2003 23:59:59'.