I’m quite new to SQL Server and was wondering if it was possible to run a query like this. I have a table with the following fields: USER, WORKSTATION, DURATION (seconds)
Each USER can use multiple workstations and each workstation can be used by multiple USERs.
In the table I have records like:
USER1 - WKS1 - 6500 USER1 - WKS2 - 3600 USER1 - WKS2 - 1200 USER1 - WKS2 - 1200 USER1 - WKS3 - 3600 USER2 - WKS1 - 8500 USER2 - WKS2 - 1000 USER2 - WKS2 - 8000 ... ...
I would like a result that groups the user and the most used workstation, example:
USER1 - WKS1 USER2 - WKS2 ... ...
As specified in the DURATION field in seconds.
There’s a way? I’ve tried but I think it takes nested queries and I’m not very knowledgeable. Thank you!
Advertisement
Answer
Think about it like this. You want the user and the workstation on which they spent the longest duration. When it comes to duration, longest is synonymous with maximum. So, we want the maximum duration by user.
SELECT user, MAX(duration) AS max_duration FROM the_table GROUP BY user;
This gives us the max duration by user.
Then, we want to identify which workstation had that value (max_duration) in the duration column. So, we use the query above as a filter for another query:
SELECT x.user, x.workstation, x.duration
FROM the_table x
INNER JOIN (SELECT user, MAX(duration) AS max_duration
FROM the_table
GROUP BY user) y
ON x.user = y.user AND x.duration = y.max_duration;
This gives us the required result.