CODEWITHSUNDEEP
sqlsql-server-2008-r2
Mehrdad Kamelzadeh
Mehrdad Kamelzadeh

Reputation: 1784

Sql 2008R2 Max()

I have a function in which I have written the following lines.

DECLARE @MaxLetterID BigInt;
DECLARE @MaxObserverLetterID BigInt;
DECLARE @MaxBillLetterID bigInt;

SET @MaxLetterID = (SELECT     MAX(LetterNO) AS Expr1
                    FROM         Lab
                    WHERE     (LetterNO BETWEEN @Min AND @Max));

SET @MaxObserverLetterID = (SELECT     MAX(LetterNO) AS Expr1
                    FROM         ObserverCoReq
                    WHERE     (LetterNO BETWEEN @Min AND @Max));

SET @MaxBillLetterID =(SELECT     MAX(LetterNO) AS Expr1
                    FROM         BillLetters
                    WHERE     (LetterNO BETWEEN @Min AND @Max));

SET @FinalID=MAX(@MaxLetterID,@MaxObserverLetterID,@MaxBillLetterID)

am I right with the last line? can I use Max function like this?

Upvotes: 1

Views: 55

Answers (1)

juergen d
juergen d

Reputation: 204924

No, you can't do that. You would need a greatest funxtion which SQL Server does not have. But this

select @FinalID = max(c)  
from 
(
     select @MaxLetterID as c
     union  
     select @MaxObserverLetterID as c
     union
     select @MaxBillLetterID as c
) x

SQLFiddle demo

Upvotes: 3

Related Questions