count, not count? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

count, not count?

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-10 : 16:21:50

Ugh. So what i want to do is find the instances in a table where the arrestno occurs more than once. I need all columns for those rows where that happens. I tried using a variation of what Sunitabeck supplied me with:


;WITH cte AS
(
	SELECT ARRESTNO,FSN,COUNTS,
		COUNT(*) OVER (PARTITION BY ARRESTNO,FSN) AS NewCounts
	FROM
		[dbo].[ARR_AFSS]
)
SELECT * FROM cte;
--UPDATE cte SET COUNTS = NewCOUNTS;

But it's not meant to do that.

I have 40 minutes to get this done, so please don't take this as an invitiation for me to "learn something" by waiting for me to figure it out on my own. Aint happenin this time.

help?

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-10 : 16:26:25

34 minutes. i'm dyin here.

pduffin
Yak Posting Veteran

68 Posts

Posted - 2012-05-10 : 17:38:49

select *
from [ARR_AFSS]
where ARRESTNO in
(
select ARRESTNO
from ARR_AFSS
group by ARRESTNO
having count(*) > 1
)

Likes to run, hates the runs!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources