1 Reply Latest reply on Feb 23, 2014 2:01 PM by Aaron Clancy

    using custom sql to select MAX values

    Dan Gerena

      My data looks like this:

      Patient          Facility          Date        

      Jim               Hospital A     1/1/2012

      Jim               Hospital A     1/2/2012

      Jim               Hospital B     1/3/2012

      Mary            Hospital A     1/2/2012

      Mary            Hospital B      1/3/2012

      Mary            Hospital B      1/4/2012

      Mary            Hospital B      1/5/2012


      I only want to keep the MAX record for each patient based on the most dates spent at the respective Hospital.


      For instance:

      Patient          Hospital

      Jim               Hospital A

      Mary           Hospital B


      I'm trying to do this using CUSTOM SQL connection, as I don't have privs to create a view In the datasource I'm querying, and ultimately I need to use data blending to link to another connection in my workbook.


        • 1. Re: using custom sql to select MAX values
          Aaron Clancy

          I'm using the JET engine for this query (if you're using a different connector you may need to modify the query):


          select [A].[Patient],

          (select top 1 [B].[Hospital] as [Hospital] from [TABLE] [B] where [A].[Patient] = [B].[Patient]

              group by [B].[Patient],[B].[Hospital] order by count(*) desc) as [Hospital]

          from [TABLE] [A] group by  [A].[Patient]


          Original Table

          Screen Shot 2014-02-23 at 4.01.17 PM.png


          Result of Custom SQL

          Screen Shot 2014-02-23 at 4.00.45 PM.png


          Let me know if this works for you