how to get only duplicate rows from table
-
Why are you using a temp table?
Just a comment, there's no use in putting an Order By on an insert, unless the target table has an identitiy
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
yaa there is no use for putting order by but for checking purpose i put order by.
i for got to delete.
Malleswarareddy
I.T.Analyst
MCITP(70-451) -
malli o2o2020
2 malli 878hjh8
3 reddy ghhg5
4 reddy 56656
5 eswa ttt656
6 rama ettt
ok lets assume column one is called id, 2 is idtext, and 3 is idcode
select * from [tablename] a where idtext in
(select idtext from [tablename] b group by idtext having count>1)
job done!
Enjoy
Andy
-
Here's another way
WITH cteDupeName AS (SELECT RowNum, Name, SomeValue,
COUNT(*) OVER(PARTITION BY Name) AS cn
FROM #TestData)
SELECT RowNum, Name, SomeValue
FROM cteDupeName
WHERE cn>1
ORDER BY RowNum;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Here is the Query for u r solution....
select * from tblSapmple
where names in (
select names
from tblsample
group by names
having count(names)>1 )
-
DECLARE @table TABLE
(
RowNum INT , Name VARCHAR(100), SomeValue VARCHAR(100)
)
INSERT INTO @table
SELECT 1,'malli','o2o2020' UNION ALL
SELECT 2,'malli','878hjh8' UNION ALL
SELECT 3,'reddy','ghhg5' UNION ALL
SELECT 4,'reddy','56656' UNION ALL
SELECT 5,'eswa','ttt656' UNION ALL
SELECT 6,'rama','ettt'
SELECT
* FROM
@table T1
WHERE
EXISTS
(
SELECT 1 FROM
@table t2 WHERE T1.NAME = T2.NAME
GROUP BY NAME HAVING COUNT(1) > 1
)
Regards,
Mitesh OSwal
+918698619998
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply