Table variable with group by

Sai Reddy

8y774

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jaimin Shethiya6y ago
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.
0
Mageshwaran R6y ago
Hi,
try this code:
DECLARE @ID INT
SELECT @ID=MAX(VAL)  from TheAssignmentPuzzle
SELECT @ID
return 30;
0
Jayesh Vaghasiya8y ago
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

0
Laxmidhar Sahoo8y ago
hi
try it

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

0
Suraj Kumar8y ago
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
+1