Table variable with group by

Question

Hi, SELECT * FROM TheAssignmentPuzzle ID Val 1 10 2 30 3 29 Declare @id int Select @id=max(vals) from TheAssignmentPuzzle group by id select @id It returns 29, but in the table max value is 30.

Jaimin Shethiya · Answer

Hello Sai Reddy, can you please try below script and let me know it's working or not. Declare @id int Select @id = max(val) from TheAssignmentPuzzle group by val select @id You have wrong write the group by statement. group by id means they have grouping the id wise and shows the max id is 3 and value is 29 so result is showing 29. Thanks.

Jayesh Vaghasiya · Answer

try this DECLARE @TBL TABLE(ID INT,vals int) INSERT INTO @TBL(ID,vals) VALUES(1,10) INSERT INTO @TBL(ID,vals) VALUES(2,30) INSERT INTO @TBL(ID,vals) VALUES(3,29) Declare @id int Select @id=max(vals) from @TBL group by vals select @id

Suraj Kumar · Answer

Hello Sai, As you put group by id then 3 is the max value of id so it is giving the value which is against the max id. If you put other value like 10 or 30 against id 3 then your query will give the result 10 or 30 respectively. In group by id query every value is max against that id. insert into #TheAssignmentPuzzle values (1, 30); insert into #TheAssignmentPuzzle values (3, 10); insert into #TheAssignmentPuzzle values (2, 29); According to above value it will give 10 as your result. insert into #TheAssignmentPuzzle values (1, 30); insert into #TheAssignmentPuzzle values (3, 30); insert into #TheAssignmentPuzzle values (2, 29); This will give result 30. Regard Suraj

Mageshwaran R · Answer

Hi, try this code: DECLARE @ID INT SELECT @ID= MAX (VAL) from TheAssignmentPuzzle SELECT @ID return 30;

Laxmidhar Sahoo · Answer

hi try it select MAX(val) RANK() over (order by ID desc) as ranks from TheAssignmentPuzzle where ranks = 1