Removing duplicate records from the resident table using distinct clause

[#1]

Hi All,

I am trying to remove all the duplicates and bring in only distinct rows from the resident table below but it still is bringing in all the duplicates.  See below I have Noconcatenate clause used too.  Any leads will be very much appreciated

Thanks,

Monica

Table2:

NoConcatenate

Load

Distinct *

Resident Table1;

Drop Table Table1;

[sunny_talwar]

This should have only brought DISTINCT, but DISTINCT row of all your columns. Can you share a sample where this isn't working?

[#3]

Yes I will do it i another post as right now I am unable to retrieve anything from server but can you also let me know how can I get distinct rows based on some columns not all?  I was thinking if there is a syntax for where clause that would do that?

Thanks

Monica

[nabhiram]

Hi Monica,

Your code should work correctly. I tried with my own.

Table1:
LOAD * INLINE [
    A, B, C
    1, 1b, 1c
    2, 2b, 2c
    2, 2b, 2c
    3, 3b, 3c
    4, 4b, 4c
    4, 4b, 4c
    5, 5b, 5c
];

Table2:
NoConcatenate
Load
Distinct *
Resident Table1;
Drop Table Table1;

Output is:

Send your sample code, then we can find out what's wrong with it

Thanks

[jagan]

Hi,

If you give

LOAD DISTINCT *

Then the distinct is considered for all the columns but not for a single column.  For example

ID, Country

1, India

1, USA

If you give

LOAD DISTINCT *

FROM DataSource;

You will get both the rows even the ID is same, becase the country field is having different values.  If you just want one row  then

LOAD

*

FROM Datasource

WHERE Exists(ID);

It will just load only one row.

Regards,

Jagan.

[swuehl]

Monica Chhabra wrote:

Yes I will do it i another post as right now I am unable to retrieve anything from server but can you also let me know how can I get distinct rows based on some columns not all?  I was thinking if there is a syntax for where clause that would do that?

Thanks

Monica

Maybe like

Table:

LOAD

     Field1 & Field2 as Key,

     Field1,

     Field2,

     Field3

FROM ....

WHERE NOT EXISTS(Key, Field1 & Field2);

DROP FIELD Key;

Only first occurence of Key value will be loaded into the resident table. Field3 is not considered for distinctness.