Gettin error in SQL Server while determining maximum age in a table

Question

We can try to get the maximum age simply with

SELECT TOP 1 age FROM Head1 ORDER BY Age DESC

But I tried using a while loop in SQL Server

the 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
 The SELECT statement, which assigns a value to a variable, should not be combined with data retrieval operations.

Msg 102, Level 15, State 1, Line 7
 Invalid syntax next to '@maxo'.

I'm kinda stuck here. Please help the guys .....

Answer 1

declare @a int, @m int, @maxo int 
declare @name NVARCHAR(100)
set @maxo = 0;
while(@a<10)
begin
    select @name = name, @m = age from head1 where ID = @a;
     if @m>@maxo
       SET @maxo = @m;
       set @a=@a+1;
END
PRINT @name +  ',' + CAST(@maxo AS NVARCHAR(50))

ALTERNATIVE WAY

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

Answer 2

There are two problems:

Problem 1:

, , , .. , .

:

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

2:

, , . max(), ,

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

, , .

Answer 3

.

name , @m ( name - , ),

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

to

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

, - , , :

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

, head1 , ID = @a. , .

- .

Answer 4

(@a<10), 10?, , , :

DECLARE @rows bigint
SET @rows = (SELECT COUNT(1) FROM head1)

; (@a < @rows).

---

Now the second problem is what you are using ID = @a, which is an invalid clause for deleted rows in your table or spaces. To solve this problem using a kind of suggestion you have to find max(ID)again and again.

---

I can simply suggest you use this query, but without WHILE:

DECLARE @max int
SET @max = 0

SELECT @max = CASE WHEN @max < age THEN age ELSE @max END
FROM head1

If you want to use WHILEin your path, you will need a field ROW_NUMBER()similar to this in your code:

SET @m = (SELECT h1.age 
          FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY head1.age) AS rn 
                FROM head1) h1 
          WHERE h1.rn = @a;

instead

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