Gettin error in SQL Server while finding out max age in a table

Question

We can try to get max age simply by using

SELECT TOP 1 age FROM Head1 ORDER BY Age DESC

But I've tried using while loop in SQL Server

code

declare @a int, @m int, @maxo int;
set @maxo = 0;
while(@a<10)
begin
    select name, @m = age from head1 where ID = @a;
    if @m>@maxo
       @maxo = @m;
    set @a=@a+1;
end
print @maxo

error

Msg 141, Level 15, State 1, Line 5
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '@maxo'.

I am sort of stuck here. Please help guys.....

Answer 1

There are two issues:

Issue 1:

The error which you are getting is self explanatory ie, you cant select the column while you are assigning the values to the variable.

You can resolve it like this:

select @name = name, @m = age from head1 where ID = @a;

Issue 2:

In this I dont think you need that query at all to find the max age from your table. You can simply use max() function to find the max age from your table like this

SELECT Name, Age FROM Head1 WHERE Age = (SELECT MAX(Age) FROM Head1)

Using a loop is inefficient as well as it will create a performance bottleneck if your table is huge.

Answer 2

Exception text is self-explanatory.

Since you can't retrieve name in the same statement where you're assigning @m (and actually you're not using this name anywhere - so it looks you don't need it), you have to change this line

select name, @m = age from head1 where ID = @a;

to

select @m = age from head1 where ID = @a;

Or, if you really need some name, it should be assigned to some variable too, not only selected:

select @n = name, @m = age from head1 where ID = @a;

But in general this will not work, since there can be multiple records in head1 meets condition ID = @a. Assigning value to variable will only work if query returns only single row.

Note - using loop is very unefficient way of finding max value.