In a SQL Server query, we sometimes have nested queries that use values from the outer select statement, but if those values are used in the Group By, it could return more records than we expect.

The following example shows how to handle these types of queries without using Group By.

[SQL Server - Using Group By]

CFFCS | CarrzSynEdit: | SQL Script

Select (Select Col1 from Table2 where Col3=Table1.Col3) as Col1, 
Col2 from Table1 where Col4=1 Group by Col2, Col3

The above will return more records.

What we do is wrap our column in a max() or min(). Using this method, we no longer have to use the Col3 in the Group By.

[SQL Server - Max or Min to return records without using Group by]

CFFCS | CarrzSynEdit: | SQL Script

Select (Select Col1 from Table2 where Col3=min(Table1.Col3)) as Col1, 
Col2 from Table1 where Col4=1 Group by Col2

You will have to test it to find out which one you need. Either MIN or MAX.