| Author |
 Topic  |
|
|
darms21
Yak Posting Veteran
53 Posts |
 Posted - 10/09/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/09/2012 : 13:20:30
|
| What is the rule that you are using? How did you eliminate Joe from the list? |
 |
|
|
darms21
Yak Posting Veteran
53 Posts |
Posted - 10/09/2012 : 13:37:35
|
| I want to only select the most frequent occurrence of a particular user for each computer_id. |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 10/09/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/09/2012 : 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
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 10/09/2012 : 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 |
|
|
| |
Topic |
|
Counting help
