Skip to content
Advertisement

SQL Query group by for a beginner

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:

I would like a result that groups the user and the most used workstation, example:

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.

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:

This gives us the required result.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement