Assigning values to SQL variable using Select Query

Assigning a value to SQL variable:

Declare @date Datetime

set @date = getdate()

We can achieve the same using simple select statement:

select @date = getdate()

If we try to do the same from a result of query, then it will be

select @date = DateCreated from SomeTable where PrimaryKey = SomeValue

We are sure that this query will return only one record, so this statement is valid and always returns expected result. In a scenario, where the result is more than one then which value will be assigned to that variable. Will it be first record or last record or anything in between?

declare @empid char(8)

select top 10 @empid = empid from Empmaster where DeptID = 'A' order by empid

select top 10 * from Empmaster where DeptID = 'A' order by empid

select @empid

It will always get assigned to the last result data. So to be on the safe side we need to put the order by clause and use the appropriate order as ascending or desending considering the fact it always pull the last record. Otherwise we need to use the “Top 1” so it always use the value of first record in the result with proper sorting.