SQL Query group by for a beginner

Question

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!

Answer 1

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.