| sql help [message #373189] |
Tue, 03 April 2001 06:21  |
Raghavendran S
Messages: 1
Registered: April 2001
|
Junior Member |
|
|
Hi
I have a table with 2 columns which has data as follows.
column a | column b
---------------------------
r 60
r 60
r 60
s 50
e 30
How do i retrieve only those rows which have duplicate values
say in this case r and 60 appearing 3 times.
thanx
Raghavendran
|
|
|
|
| Re: sql help [message #373190 is a reply to message #373189] |
Tue, 03 April 2001 07:52  |
Balamurugan.R
Messages: 91
Registered: March 2001
|
Member |
|
|
Hi,
Try this,
select * from table_name where rowid not in(select max(rowid) from table_name group by columnname1);
With warm regards,
Balu
|
|
|
|
| Re: sql help [message #373199 is a reply to message #373189] |
Tue, 03 April 2001 22:56 |
Naresh
Messages: 27
Registered: March 2001
|
Junior Member |
|
|
Try this :
select * from emp where rowid in
(select min(rowid)
from emp group by empno
having count(*) > 1);
|
|
|
|
| Re: sql help [message #373207 is a reply to message #373189] |
Wed, 04 April 2001 04:36 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Why complicate matters.
Why not try
Select column_a,column_b
from table
group by column_a,column_b
having count(*) >1
|
|
|
|
| Re: sql help [message #373213 is a reply to message #373207] |
Wed, 04 April 2001 09:03 |
Joachim Lindner
Messages: 30
Registered: February 2001
|
Member |
|
|
Hi John,
just to make things clear ...
The difference between your proposal and mine is that yours returns 1 row for each group of duplicates.
Mine returns ALL the rows that are included in a group of duplicates but prevents rows without duplicates from being returned.
I don't think that this is a question of things being complicated or not. It is a question of solving two different problems.
But, ... I think Raghavendran will be happy anyway.
Cheers, Joachim
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
