SQL:Find one primary key of each duplicate records

[xodeblack]

I have table FEE,There are 9 rows in this Fee table PK ........ FEE ........... TRANS. ID 11 ........ 5000$ ........ 2222 12 ........ 9000$ ........ 2222 13 ........ 1000$ ........ 2222 14 ........ 2000$ ........ 7777 15 ........ 1000$ ........ 7777 16 ........ 1000$ ........ 7777 17 ........ 2500$ ........ 9999 18 ........ 4100$ ........ 9999 19 ........ 4500$ ........ 9999 I want to fetch the only one primary key of each duplicate 'trans. id' For example: The result(PK) should be 11,14,17 OR 13,14,17 OR 14,14,17 ...... ...... Duplicate Trans. id are 2222,7777,9999.

[Ashfield]

try this

select min(PK) from FEE
where Trans.ID in (select distinct Trans.ID from FEE)
group by Trans.ID

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[xodeblack]

Hi Ashfield First of all thankz for your reply It returns only one PK....I want to get one PK of each duplicate record .... According to last example I want one PK of 2222(Trans. ID),one PK of 7777(Trans. ID) and one PK of 9999(Trans. ID) so one possible result is 11,14,17 Other possible result is 12,14,17 One more possible result is 13,14,17 ......... Now I think you understand what I want waiting your reply

[Coding C]

select min(PK),Trans.ID from FEE
group by Trans.ID

try this

Coding C# ExciteTemplate

[David Skelly]

Why do you need the WHERE clause? Can't you just do:

select TransId, min(PK) from FEE group by TransId

[Ashfield]

You can if you also want the transid (which I suspect is the case), but the OP just wanted the PK :)

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[Ashfield]

Thats odd. I used this:

create table #b1(pk int identity, refid int)

insert into #b1 (refid)
select 1
union all
select 2
union all
select 3
union all
select 1
union all
select 2
union all
select 3

select min(PK) from #b1 where refid in (select distinct refid from #b1)group by refid

and it gave me 1, 2 and 3. I'm using sql server 2005, what are you using?

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[xodeblack]

I am using SQLServer 2005

[Ashfield]

So does my test script work for you?

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP