-
Identifying the duplicate values with a combination of 2 col
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]
-
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);
Cheers!
OraKid.
-
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);
Regards,
Vijay R.
-
Or try this one:
select userid
, course
, count(1)
from user_info
group by userid
, course
having count(1) > 1;
Regards
Ben de Boer
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|