max date in a subset

• timscronin

  SSCertifiable

  Points: 6851

  More actions

  May 20, 2010 at 9:43 am

  #221794

  I have some data in a table called loadit with an rxno (not unique), patient, location, filldate. There are 2 records for each rxno, but the filldate different, I only need to keep the

  records with the max filldate. DO I need to do a self-join and then use the max(filldate)

• kramaswamy

  SSCoach

  Points: 18181

  More actions

  May 20, 2010 at 9:50 am

  #1170254

  you can also do something like:

  with CTE as

  (

  SELECT rxno, patient, location, filldate, ROW_NUMBER() OVER (PARTITION BY rxno ORDER BY filldate DESC) as rowNum

  )

  SELECT rxno, patient, location, filldate FROM CTE WHERE rowNm = 1

• Ben Teraberry

  SSChampion

  Points: 11845

  More actions

  May 20, 2010 at 12:13 pm

  #1170386

  If you need something more inline you can also just do something like this:

  select rxno,

  patient,

  location,

  filldate

  from loadit l

  where l.filldate =

  (

  select max(sq.filldate)

  from loadit sq

  where sq.rxno = l.rxno

  )

  └> bt
  

  ---

  
  Forum Etiquette: How to post data/code on a forum to get the best help[/url]