Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.09 seconds. Powered By: Snitz Forums 2000