Picking just 1 value of a Unique ID

[Anonymous]

Hello all, I am building my first app and I'm almost there but I need help. I have about a 100,000 rows and approx 3 thds have duplicate primary IDs. I need to filtered the ID column down to a unique ID per row. I do not care which other values are removed. It's just important to have a unique set of IDs...See below.

ID          Cd          Name

1261        TT           John

1261        FF            John

1261       RR           John

2              TT           Shawn   

3              RR          Billy

I would like to see...

ID               Cd          Name

1261            TT          John

2                   TT         Shawn

3                  RR         Billy

This information is in a straight table. Best approach to handle this situation?

[maxgro · Accepted solution]

t1:

load * inline [

ID  ,    Cd,          Name

1261    ,  TT  ,        John

1261      ,  FF  ,        John

1261      ,RR    ,      John

2          ,  TT  ,        Shawn

3            ,  RR  ,      Billy

3            ,  RR  ,      Billy

3            ,  RR  ,      Billy

3            ,  RR  ,      Billy

]

;

store t1 into tmp.qvd (qvd);

DROP Table t1;

t1: load * from tmp.qvd (qvd) where not exists(ID);

[rwunderlich]

Hundred of thousands of rows is not much work for QV. So yes, I think INNER JOIN would perform adequately. I think Massimo's suggestion  of NOT EXISTS() is very clever and worth a try as well.

-Rob