RowNo() does not work with GROUP BY or DISTINCT

[Anonymous]

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;

[sunny_talwar · Accepted solution]

This sort of works also... again not straight forward, but the problem is that RowNo() creates a non-distinct value for each row before the Distinct can filter out duplicates.

QUALIFY RowId;

Table:

LOAD * INLINE [

    F1

    10

    10

    11

    12

    12

];

TableUniqueWithId:

NoConcatenate

LOAD DISTINCT *

RESIDENT Table;

FinalTable:

NoConcatenate

LOAD RecNo() as RowId, *

Resident TableUniqueWithId;

DROP Table TableUniqueWithId;

[Anonymous]

You're right - the same seems to apply to SQL as well (for MSSQL at least.) Simply the order in which the aggregates/distinct and RowNo() functions are being evaluated.

Thanks.