SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Counting help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

darms21
Yak Posting Veteran

54 Posts

Posted - 10/09/2012 :  13:06:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/09/2012 :  13:20:30  Show Profile  Reply with Quote
What is the rule that you are using? How did you eliminate Joe from the list?
Go to Top of Page

darms21
Yak Posting Veteran

54 Posts

Posted - 10/09/2012 :  13:37:35  Show Profile  Reply with Quote
I want to only select the most frequent occurrence of a particular user for each computer_id.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2870 Posts

Posted - 10/09/2012 :  14:37:14  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/09/2012 :  15:01:59  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2870 Posts

Posted - 10/09/2012 :  15:17:21  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000