[RESOLVED]Selecting Duplicate Records

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)

[RESOLVED]Selecting Duplicate Records

Author

Topic

terbs
Starting Member

29 Posts

Posted - 2007-06-20 : 20:48:17

I know, there are topics on this already I searched and tried it myself but it didnt seem to work. All I want is some SQL which searches a field to see if that value already exists. Heres something I tried


SELECT RASUBID, COUNT(SERIAL)
FROM TABRAsub
group by SERIAL
HAVING COUNT(SERIAL)>1

with the following error..

Msg 8120, Level 16, State 1, Line 1
Column 'TABRAsub.RASUBID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So basically im just after records where the same SERIAL exists twice in two different records..

cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-20 : 21:13:18

[code]
SELECT RASUBID, COUNT(SERIAL)
FROM TABRAsub
GROUP BY ~~SERIAL~~ RASUBID
HAVING COUNT(SERIAL)>1
[/code]

terbs
Starting Member

29 Posts

Posted - 2007-06-20 : 21:34:56

thanks for the quick reply khtan.. this still does not work..

I get no errors, but I recieve no results. I know there a duplicate serials in my DB as I have manually put some in to test. When I do a SELECT statement using the serial key as the WHERE clause I get multiple records coming back to me..

cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-20 : 22:21:59

quote:
So basically im just after records where the same SERIAL exists twice in two different records..

I see this is what you want.

use this

SELECT SERIAL, COUNT(*)
FROM  TABRAsub
GROUP BY SERIAL
HAVING COUNT(*) > 1

terbs
Starting Member

29 Posts

Posted - 2007-06-20 : 22:58:16

ok I think thats working, but is there anyway to display the RASUBID aswell? Everytime I add it to the select statement I get an error..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-20 : 23:02:06

[code]SELECT t.RASUBID, t.SERIAL, d.cnt
FROM TABRAsub t
INNER JOIN
(
SELECT SERIAL, cnt = COUNT(*)
FROM TABRAsub
GROUP BY SERIAL
HAVING COUNT(*) > 1
) d
ON t.SERIAL = d.SERIAL[/code]

terbs
Starting Member

29 Posts

Posted - 2007-06-20 : 23:49:07

champion, thanks khtan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources