January 25, 2010 at 12:13 am
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 recoverability
January 25, 2010 at 12:23 am
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)
January 26, 2010 at 6:20 am
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
January 26, 2010 at 6:27 am
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/61537January 27, 2010 at 5:04 am
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 )
January 27, 2010 at 6:36 am
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
@tableT1
WHERE
EXISTS
(
SELECT1 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