Identifying the duplicate values with a combination of 2 col

[srinivasm]

Hi

How we can get the duplicate values of the combination of two coulmns?

normally in a table if we want to know what are all the duplicate values of a single column, I have tried like this

Table: user_info

userid -- password -- age
a --- p -- 26
b --- p -- 24
c --- p -- 16
d --- p -- 46
e --- p -- 42
a --- p -- 26
b --- p -- 56


select userid,count(userid) from user_info group by userid having count(userid) > 1;

result:

userid -- count(userid)
a 2
b 2


But now i have a table like follows


userid -- course --- location -- score
a -- course1 ---xxx --- 85
a -- course2 ---xxx --- 85
a -- course3 ---xxx --- 85
b -- course1 ---xxx --- 85
b -- course2 ---xxx --- 85
b -- course3 ---xxx --- 85
a -- course1 ---xxx --- 85

Now i want to know which row has the duplicate values when we take userid and course as combination.


Thanks for any help

Srinivas M





[Edited by srinivasm on 01-07-2002 at 10:27 PM]

[balajiyes]

just check this:

select * from msg a where rowid > (
select min(rowid) from msg b where
a.es = b.es and
a.msg_id = b.msg_id);

[vijayr]

Hi Srini,

Try this.

select * from TABLENAME where rowid in (select min(rowid) from TABLENAME group by COLNAMES having count(*) > 1);

or

select * from TABLENAME where rowid in (select max(rowid) from TABLENAME group by COLNAMES having count(*) > 1);

[bensr]

Or try this one:
select userid
, course
, count(1)
from user_info
group by userid
, course
having count(1) > 1;

[santoshym]

Hi

Try this link

http://www.dbasupport.com/forums/sho...threadid=11102

Regards