Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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);
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