SQL Server Query

Question

Hello,

I have dummy data below

DECLARE @TBL TABLE(col1 VARCHAR(100),col2 varchar(100))

INSERT INTO @TBL VALUES ('server-1','23658512')

INSERT INTO @TBL VALUES ('server-2','24658512')

INSERT INTO @TBL VALUES ('server-2','14658512')

the result from select * from @TBL

Now i want to add one column contain higher number of col2.

The higher number of all col2 is 24658512, so the expected result will be like :

need some help from expert here to modify the select query.

Answer 1

Hi @Handian Sudianto

Try this:

SELECT *,MAX(col2)OVER(PARTITION BY NULL)AS COL3
FROM @TBL
--Or
SELECT *
FROM @TBL CROSS APPLY (SELECT MAX(col2)AS COL3 FROM @TBL)T

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Use OVER clause to implement it, like this

SELECT *,  CONVERT(VARCHAR(100) ,MAX(CONVERT(BIGINT, col2)) OVER()) AS col3 FROM @TBL