2 Replies Latest reply: Dec 7, 2017 9:02 AM by Roman Puttkammer RSS

    RowNo() does not work with GROUP BY or DISTINCT

    Roman Puttkammer

      I'm generating a new table with unique records along with a record identifier, and I'm puzzled as to why I can't combine RowNo() with either DISTINCT or GROUP BY. Autonumber() on the other hand seems to work with GROUP BY.

       

      Any ideas why that is?  The entire script included below.


      Thanks!

       

       

       

       

      QUALIFY RowId;

      Table:

      LOAD * INLINE [

          F1

          10

          10

          11

          12

          12

      ];

       

      // This works (but missing the ID column)

      TableUnique:

      NoConcatenate

      LOAD DISTINCT *

      RESIDENT Table;

       

      // Not working (shouldn't this work?) - includes all (duplicate) rows

      TableUniqueWithId:

      NoConcatenate

      LOAD DISTINCT RowNo() as RowId, *

      RESIDENT Table;

       

      // Not working (shouldn't this work?) - RowID is zero for all records

      TableUniqueWithIdAlt:

      NoConcatenate

      LOAD max(RowNo()) as RowId, *

      RESIDENT Table

      GROUP BY F1;

       

      // Only this works.

      TableUniqueWithIdAlt2:

      NoConcatenate

      LOAD DISTINCT autonumber(F1) as RowId, *

      RESIDENT Table;