I need a list of unique keys WITH a second field (description)

[Guest.Visitor]

I'm trying to SQL a file with two fields and I need to list field1 and field2 only once field1 repeats and field2 is unique. Example as follows: SELECT DISTINCT field1 FROM filename GROUP BY field1 ORDER BY field1 I don't want field2 in the group by. Thanks.

[Guest.Visitor]

Joseph, Anytime I want to find duplicated "key" fields, I use this type of logic:

Select field1,count(*) from file group by field1 having count(*)>1 

Bill

[Guest.Visitor]

Thanks Bill. I tried that an it does give me unique keys, but I also need to view field2 from that file. Thanks

[Guest.Visitor]

So, if I understand you correctly, you want to know all of the field1 and field2 values, when there are duplicate field1's? Try this:

Select fielda,fieldb from file where fielda in (select fielda from file group by fielda having count(*)>1)

Bill

[Guest.Visitor]

Thanks Bill, I did try that before submit the question. It seems fielda is always in the subquery so it will display as many times as it occurs in the file. Thanks anyway.

[Guest.Visitor]

Joseph, If you -really- did try what I submitted, then something is wrong with your SQL package and may need some PTF's. Look again at the code, did you put the Having clause in the sub-select? The sub-select will only feed those field values that have more than one occurrance in the file and then will list every record set that contains that field value. Which I believe is what you asked for. Bill

[Guest.Visitor]

I did do a copy and paste and replaced the field1 field2 and filea with values. I only want to see one occurence of the fielda. If you try it you will see that fielda repeats. I only want to see the first occurence of it.

[Guest.Visitor]

Ah, the key phrase: you only want to see the first occurrance. Now, do you want to see the first occurrance as it exists in the file? Or is there some other field value that would specify the first occurrance in a file? OK, I haven't tested this, but this in theory should find the first occurrance in a file:

 Select fielda,fieldb from file F1 where rrn(file)=(Select min(rrn(file)) from file F2 where f2.fielda=f1.fielda having count(*)>1) 

If this is a large file, this might be a long running statement. Bill

[Guest.Visitor]

the sintax is incorrect. I recieved an error : Column qualifier or table file undefined. I think your on the right track. Thanks.

[Guest.Visitor]

I think that maybe the RRN() statement may require the qualifier F2, not FILE. So, change both RRN statements to reflect the qualifiers F1 and F2. Bill