CODEWITHSUNDEEP
sqlsql-server
user7361363
user7361363

Reputation:

SQL Server, include columns that are not in group by statement

I have a permanent problem,
lets assume that I have a following columns: 

T:A(PK), B, C, D, E

Now,

select A, MAX(B) from T group BY A

No, I cant do: 

select A, C, MAX(B) from T group BY A

I don't understand why - when in comes to AVG or SUM I get it. However, MAX or MIN is getting from exactly one row.

How to deal with it?

Upvotes: 0

Views: 348

Answers (4)

Bee_Riii
Bee_Riii

Reputation: 1039

Your question isn't very clear in that we aren't sure what you are trying to do.

Assuming you don't actually want to do a group by in your main query but want to return the max of B based on column A you can do it like so. 

select A, C,(Select Max(B) from T as T2 WHERE T.A = T2.A) as MaxB from T

Upvotes: 0

SKLTFZ
SKLTFZ

Reputation: 950

try this. it can help you find the MAX by just 1 column (f1), and also adding the column you wanted(f3) but not affecting your MAX operation

SELECT m.f1,s.f2,m.maxf3 FROM
(SELECT f1,max(f3) maxf3 FROM t1 GROUP BY f1) m
CROSS APPLY (SELECT TOP(1) f2,f1 FROM t1 WHERE m.f1 = f1) s

Upvotes: 0

Rahul
Rahul

Reputation: 77896

That's cause columns included in the select list should also be part of group by clause. You may have column which re part of group by but not present in select list but vice-versa not possible.

You generally, put only those columns in select clause on which you want the grouping to happen.

Upvotes: 0

shA.t
shA.t

Reputation: 16968

You can use ROW_NUMBER() for that like this:

select A, C, B
from (
    select *
        , row_number() over (partition by A order by B desc) seq
        --                       group by ^      max(^)
    from yourTable ) t
where seq = 1;

Upvotes: 1

Related Questions