SOLVED

Incorrect Result Set

Copper Contributor

Hi @All

Please explain why the first select statement returns the data when there is no available record in the table 

Declare @Temp Table (EmployeeName Varchar(100),DOB Datetime)

Select 'Test',MAX(DOB) From @Temp

 

Select 'Test',MAX(DOB) From @Temp
Group by DOB

 

Select * From @Temp


Thanks

1 Reply
best response confirmed by Javier Villegas (MVP)
Solution

@ahamed0820 

 

The results are correct because you are using an aggregate function, which allows the 'Test' literal to generate a result set row that just happens to have a NULL value for the aggregate result.

 

If you were not performing an aggregation - as shown in the following example, you would get no rows (which is what you seem to have incorrectly expected for the aggregate scenario).

 

SELECT 'Test', * From @Temp;

 

Cheers,

Lain

1 best response

Accepted Solutions
best response confirmed by Javier Villegas (MVP)
Solution

@ahamed0820 

 

The results are correct because you are using an aggregate function, which allows the 'Test' literal to generate a result set row that just happens to have a NULL value for the aggregate result.

 

If you were not performing an aggregation - as shown in the following example, you would get no rows (which is what you seem to have incorrectly expected for the aggregate scenario).

 

SELECT 'Test', * From @Temp;

 

Cheers,

Lain

View solution in original post