Retriving duplicate records from the table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Retriving duplicate records from the table

Author

Topic

gaurish.salunke
Starting Member

7 Posts

Posted - 2008-05-16 : 07:53:53

Hello,

I need some help on this.
I want to retrieve all the duplicate records from a particulat column.
For eg suppose i have a table named testtable

columns in the table : item_id,ref_no,title,address

now i need to check if there are any duplicate entries in the ref_no column and if there are any retrieve the records.

Gaurish Salunke
Software Developer
OPSPL

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-16 : 07:56:41

SELECT ref_no, COUNT(*)
FROM testtable
GROUP BY ref_no
HAVING COUNT(*) > 1

--
Lumbago

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-16 : 10:42:31

You want to return all records which have duplicates or you just want duplicate values alone?

If you want to retrieve all records having duplicate entries use this

SELECT * FROM Table 
WHERE ref_no in (
SELECT  ref_no
FROM Table 
GROUP BY ref_no
HAVING COUNT(*) > 1)

And if you want duplicate values alone (barring the first occurance) use this

SELECT *
FROM
(
SELECT ROW_NUMBER OVER (PARTITION BY ref_no ORDER BY item_id) AS RowNo,
* 
FROM Table 
)t
Where t.RowNo >1

gaurish.salunke
Starting Member

7 Posts

Posted - 2008-05-17 : 01:59:08

hello visakh16,

thanks for your reply.

but row_number() in giving error on sql server 2005

it says -

'ROW_NUMBER' is not a recognized function name.

What do you think is the problem?

Gaurish Salunke
Software Developer
OPSPL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-17 : 02:32:31

quote:
Originally posted by gaurish.salunke

hello visakh16,

thanks for your reply.

but row_number() in giving error on sql server 2005

it says -

'ROW_NUMBER' is not a recognized function name.

What do you think is the problem?

Gaurish Salunke
Software Developer
OPSPL

Thats because you've set the compatibilty level of your database to be <90. ROW_NUMBER() is available only in compatibilty level 90 and more. So if you want to use ROW_NUMBER() change compatibility to 90 using sp_dbcmptlevel system stored procedure

EXEC sp_dbcmptlevel <yourdbname>, 90;
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources