>I'm actually writing some questions for a T-SQL test, and I dusted off this example from the vault.
>
>This query and example (by itself) doesn't make much sense, but it does illustrate a subtle difference between 2 queries.
>
>If anyone wants to answer this, I ask one thing - don't paste the code and run it...see if you can figure out the results just by reading.
>
>Suppose I have this table...
>
>
>CREATE TABLE dbo.MaxTest (Name varchar(50), PayRate decimal(14,4))
>
>insert into dbo.MaxTest values ('Kevin', 50),
> ('Katy', 60)
>
>
>
>Here are 2 code samples. What will happen in each of the two examples? (will I get an error, will I get a result, etc.)
>
>sample 1:
>
>
>DECLARE @MaxRate decimal(14,4)
>SET @MaxRate = (SELECT MAX(PayRate) from dbo.MaxTest GROUP BY Name)
>SELECT @MaxRate
>
>
>sample 2:
>
>
>DECLARE @MaxRate decimal(14,4)
>SELECT @MaxRate = MAX(PayRate) FROM dbo.MaxTest GROUP BY Name
>SELECT @MaxRate
>
I was positive that the 1st would return an error because the query would return 2 records.
I was expecting the 2nd to fail as well for the same reason, but it just picks the last value of the name alphabetically sorted (ascending), just more or less VFP does when SET ENGINEBEHAVIOR is set to 70. Wierd.