Group by and aggregate values

 Id开发者_StackOverflow       User Val1 Val2
 11910115 Fred 123 -0.000137
 11910116 Fred 456 0
 11910117 Fred 789 0.0002
 11910118 Sue 101 8.7E-05
 11910119 Sue 102 0.000125
 11910120 Sue 103 0

The intent:

Select Id,User,Val1,Max(abs(val2)) val2
From MyTable
Group By User

What I'm after is:

11910117 Fred 789 0.0002
11910119 Sue 102 0.000125

What I'd settle for is:

11910117 0.0002
11910119 0.000125

However, I can't get around the 'is invalid in the select list because it is not contained in either an aggregate...'

---

SELECT y.Id, y.User, y.Val1, t.MaxVal
    FROM YourTable y
        INNER JOIN (SELECT User, MAX(ABS(val2)) AS MaxVal
                        FROM YourTable
                        GROUP BY User) t
            ON y.User = t.User
                AND ABS(y.Val2) = t.MaxVal
    WHERE y.User = 'Fred'

---

Every field that is not an aggregated should appear in the group by. So assuming you want to aggregate val1 too:

Select Id, User, max(Val1) val1, max(abs(val2)) val2
From MyTable
Group By Id, User

---

select Id, User, Val1, Val2
from MyTable t1
where not exists
(
   select * 
   from MyTable t2
   where t2.Id != t1.Id
   and   t2.User = t1.User
   and   abs(t2.Val2) > abs(t1.Val2) 
)

---

Give this a try

SELECT t.id, t.user, t.val1, t.val2
FROM mytable t
    INNER JOIN
        (SELECT user, MAX(ABS(val2)) AS max
        FROM mytable
        GROUP BY user) q ON t.user = q.user AND ABS(t.val2)=q.max

Not sure about how efficient this would be on your table, though.

---

select Id,
       [User],
       Val1,
       Val2
from (
      select Id,
             [User],
             Val1,
             Val2,
             row_number() over(partition by [User] order by abs(Val2) desc) as rn
      from YourTable
      -- where ... goes here
     ) as T
where rn = 1

https://data.stackexchange.com/stackoverflow/q/114300/