Correlated subquery in a select statement with group by

Question

I have a select query with group by and the select list has a correlated sub-query in it. This query returns the correct results. My question is how is this query working. Shouldn't the sub-query be part of the group by list? Could someone please explain the mechanics behind it? Thank you!

Select a.column1
      ,Max(a.column2)                         'MaxValue'
      ,Min(a.column2)                         'MinValue'
      ,(Select top 1 Column c
          From table A  a_s
         Where a_s.column1 = a.column1
           and a_s.ver <> 0
        )                                     'ColumnCValue'
  From tableA a
 Group by a.column1

Answer 1

It is not necessary. This is the correlated subquery:

(Select top 1 Column c
 From table A  a_s
 Where a_s.column1 = a.column1 and
-------^
       a_s.ver <> 0
)

The key is that the correlation clause is using a.column1. And that column is an aggregation column. In effect, the correlated subquery is run after the aggregation, so it can only use columns and expressions available after aggregation. And you are doing that, so your expression is fine.