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:
| Description | Reference | Person | Minutes |
|---|---|---|---|
| Activity A | AA32343 | Abe | 10 |
| Activity B | BB34345 | Boris | 8 |
| Activity C | CCsdeee | John | 12 |
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:
| Description | Reference | Person | Minutes |
|---|---|---|---|
| Activity A | AA32343 | Abe | 10 |
| Activity A | AA77340 | Wilson | 9 |
| Activity A | AA56341 | Carl | 4 |
| Activity B | BB34345 | Boris | 8 |
| Activity B | BB94342 | Jane | 6 |
| Activity B | BB64343 | Martha | 3 |
| Activity C | CCsdeee | John | 12 |
| Activity C | CCs5ee4 | Peter | 10 |
| Activity C | CCskee5 | Saul | 4 |
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments