SQL Query group by for a beginner-CodePudding

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!

CodePudding user response：

You can group by USER and WORKSTATION to get the total duration for each combination of these 2 columns and with FIRST_VALUE() window function get the value of WORKSTATION with the top duration for each USER:

SELECT DISTINCT [USER], 
       FIRST_VALUE([WORKSTATION]) OVER (PARTITION BY [USER] ORDER BY SUM([DURATION]) DESC) [WORKSTATION]
FROM tablename
GROUP BY [USER], [WORKSTATION];

See the demo.

CodePudding user response：

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.

CodePudding user response：

with cte as (
    Select User, 
           Workstation, 
           Max(Duration) as Duration
    from TableName
    group by User, 
             Workstation 
) 
select User, Workstation
from cte
order by User, Workstation