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 2012 Forums
 Transact-SQL (2012)
 GROUPING Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dcarva
Posting Yak Master

USA
140 Posts

Posted - 04/08/2014 :  14:58:19  Show Profile  Reply with Quote
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

USA
93 Posts

Posted - 04/08/2014 :  15:12:50  Show Profile  Reply with Quote
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.
Go to Top of Page

dcarva
Posting Yak Master

USA
140 Posts

Posted - 04/08/2014 :  15:20:45  Show Profile  Reply with Quote
Thank you very much. I will try this out.
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.05 seconds. Powered By: Snitz Forums 2000