GROUPING Question - 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 2012 Forums

Transact-SQL (2012)

GROUPING Question

Author

Topic

dcarva
Posting Yak Master

140 Posts

Posted - 2014-04-08 : 14:58:19

Hello,

I have the following query that I am struggling with. I can do this with a temp table and some loops but I am interested in learning how to do it in one query if possible.

SELECT MachineID, UserID, SUM(hits) Hits
FROM MachineUsage
GROUP BY MachineID, UserID

It might return:

machineid, userid, hits
1 2 444
1 4 111
2 5 365
2 3 3254

What I need to return is the row for the machine/user combination that has the most hits per unique machine. Out of those four, I need:

1 2 444
2 3 3254

Because machine 1 had the most hits with user 2. Also, machine 2 had the most hits with user 3.

Any help is appreciated!
Because those are the rows for each machine that has the most usage. I hope this makes sense.

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-08 : 15:12:50

hi dcarva, see below code..


DECLARE @Input TABLE
(MachineId INT,
UserId INT,
Hits INT
)

INSERT INTO @Input VALUES(1, 2, 444), (1, 4, 111), (2, 5, 365), (2, 3, 3254)

;WITH CTE AS
(SELECT *, ROW_NUMBER() OVER (PARTITION BY MachineID ORDER BY Hits DESC) AS Rnum
FROM @Input
)
SELECT MachineID, UserID, Hits
FROM CTE
WHERE Rnum = 1

Read about ranking functions @ http://sqlsaga.com/sql-server/what-is-the-difference-between-rank-dense_rank-row_number-and-ntile-in-sql-server/

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

dcarva
Posting Yak Master

140 Posts

Posted - 2014-04-08 : 15:20:45

Thank you very much. I will try this out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources