2 Replies Latest reply: Aug 19, 2011 10:51 AM by Giovana V RSS

    How to select a distinct row from resident table

      Hello,

       

      I have a resident table "TMP1" with:

       

      codclient_name
      initial_date
      1John01/01/2011
      1Paul12/12/2010
      2Georgia05/06/2011
      3Marie07/30/2011
      2Jack02/30/2011

       

      But, I need to have just one row for each "cod" depending on the max initial_date.

       

      So, my new table should have:

      1 - John - 01/01/2011

      2 - Georgia - 05/06/2011

      3 - Marie - 07/30/2011

       

      With SQL I could do:

      select cod, client_name, initial_date from tmp1 t1

      where client_name = (select top 1 client_name from tmp1 t2 where t1.cod = t2.cod order by initial_date desc);

       

      But I need to do this with my resident table and not accessing the database again.

      Do somebody have any idea how to do that?

      Thanks!!!

        • Re: How to select a distinct row from resident table

          Hi,

           

          See this code.

           

          TMP1:
          LOAD
          Cod,
          Client_Name,
          Initial_Date,
          Cod & '#' & Initial_Date AS KEY_TMP1;
          LOAD
          Cod,
          Client_Name,
          Initial_Date
          INLINE [
              Cod, Client_Name, Initial_Date
              1, John, 01/01/2011
              1, Paul, 12/12/2010
              2, Georgia, 05/06/2011
              3, Marie, 07/30/2011
              2, Jack, 02/30/2011
          ];

           

          // This table filter the max date for each code

          SELECT_CLIENT:
          LOAD
          Cod & '#' & Max_Date AS KEY;
          LOAD
          Cod,
          DATE(MAX(Initial_Date), 'MM/DD/YYYY') AS Max_Date
          Resident TMP1
          GROUP BY Cod;

           

          // This table using the function "EXISTS" to return just one information for the table TMP1

          RESULT:
          LOAD
          Cod,
          Client_Name,
          Initial_Date
          RESIDENT TMP1
          WHERE EXISTS(KEY, KEY_TMP1);

          DROP TABLE SELECT_CLIENT;
          DROP TABLE TMP1;