Correlated subquery in a select statement with group by

user_14386678 0
Viewed 255

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
correlated-subquery group-by sql-server sql
1 Answers

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.

user_11440350
Related