Counting help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Counting help

Author

Topic

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-10-09 : 13:06:45

I need to select the high count for each computer_id in the following example:

Computer_ID User
1 Kevin
2 Mark
1 Kevin
1 Kevin
1 Joe
2 Mark
2 Joe

The results should be
1 = Kevin
2 = Mark

Any idea how to get there?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-09 : 13:20:30

What is the rule that you are using? How did you eliminate Joe from the list?

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-10-09 : 13:37:35

I want to only select the most frequent occurrence of a particular user for each computer_id.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-10-09 : 14:37:14

There's probably a coller way to do this with CROSS APPLY, but this works.

DECLARE @Table TABLE (Computer_ID smallint, nUser varchar(10))

INSERT INTO @TABLE
SELECT 1,'Kevin' UNION ALL
SELECT 2,'Mark' UNION ALL
SELECT 1,'Kevin' UNION ALL
SELECT 1,'Kevin' UNION ALL
SELECT 1,'Joe' UNION ALL
SELECT 2,'Mark' UNION ALL
SELECT 2,'Joe'

SELECT *
FROM
(
select a.*,rank() over(partition by computer_id order by comps desc) as Row
from
(
select Computer_id,nUser,count(*) as comps
from @table
group by Computer_id,nUser

) a
) b

WHERE b.Row = 1

Jim
Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-09 : 15:01:59

I don't know if the cross apply is any cooler or easier to read, Jim. I sort of like your approach. But here is the cross apply - it looks a less readable to me.

SELECT
	computer_id,
	b.nUser
FROM
	@TABLE a
	CROSS APPLY
	(
		SELECT TOP 1 b.nUser,COUNT(*) AS N
		FROM @TABLE b
		WHERE b.computer_id = a.computer_id
		GROUP BY b.computer_id, b.nUser
		ORDER BY COUNT(*) DESC 
	) b
GROUP BY
	computer_id,
	b.nUser

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-10-09 : 15:17:21

I tried the CROSS APPLY first but select a.nUser instead of b.nUser which of course gave me everybody! The rank method seems to be ab it faster on this small data set

RANK
Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

CROSS APPLY
Scan count 8, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources