PDA

View Full Version : Unique records



john
08-19-2004, 02:49 AM
Hi

I am having a data consists of 10 columns in a table. Among 10 columns, one column is having duplicates and all other coulmns are different, for eg., in the below example, There are duplicates in Key but not in Name... I want to retrieve only unique records( non duplicates and one from each duplicate).

Key Name ................

24 jack
42 robet
42 rhode
42 richter
42 rammy
67 tony
67 tam
89 walter
95 wong
95 warne

OUTPUT:

Key Name
24 jack
42 robet
67 tony
89 walter
95 wong

Thanks in Advance,
John

sgrant
08-23-2004, 08:11 AM
I don't have an exact answer yet; but I have solved this kind of problem using two or three "nested" queries using aggregate functions. Like one would select the min of Key and Name, then the second would use that result and the original table - matching on the name and maybe the key field to fill in the rest of the data needed.
Hope that helps.