The object is to get the top activity that takes too much time per activity:

In mysql it should be easy:

select description, reference, person, max(minutes)
group by description

Result should bring back:

But in Microsoft SQL, it wont run, 'cause the group by does not have all the columns in the select...

What is the equivalent to the mysql query in MS SQL? If i add all the columns i need in the group by, i will get all the rows, which is not what i want

This is a very common problem, can someone find the answer and post the query and explain it in a way that the answer can be applied to almost all similar problems? Thanks in advanced

Rows in table have info like:

CodePudding user response：

use row_number()

select * from (select description, reference, person, minutes,
row_number() over(partition by description order by minutes desc) rn from table_name
) a where rn=1

CodePudding user response：

That is an invalid group by query in fact that you are doing in MySQL. How do you decide for example, it is reference 'AA32343' for 'Activity A'? Probaly just by ignoring from which row it comes. Then you could simply use an aggregation on those columns as well:

select description, min(reference) reference, min(person) person, max(minutes)
from myTable
group by description;

Or if you meant those fields come from the row with max(minutes), then:

select t.*
from myTable t
inner join (
select description, max(minutes) maxMin
from myTable
group by description) tmp on t.description = tmp.description and t.Minutes = tmp.maxMin;

CodePudding user response：

Using an analytic window function

with cte as

(select *, max(minutes) over (partition by description) as max_minutes
 from your_table)


select description, reference, person, minutes
from cte
where minutes=max_minutes;

CodePudding user response：

Issue here is that you are omitting elements, as you don't seem to care which Reference and Person to show for each grouping.

One way to get your desired results is with a CTE to get Max minutes per Description, and then append the other columns. For this example I'm getting the first Reference and Person ordered by Description:

;with cte as
(
select  Description, 
        Minutes = max(Minutes)
from myTable MT
group by Description
)
Select  C.Description,
        R.Reference,
        R.Person,   
        C.Minutes
From cte C
Cross Apply
(
    Select top 1    T.Reference,
                    T.Person
    From myTable T
    where T.Description = C.Description
    order by T.Description
) R