Mega Code Archive

 

Categories / MSSQL / Transact SQL

 

Assign value returned from a aggregate function to a variable with set statement

5> 6> CREATE TABLE OrderDetails ( 7>      OrderID int NOT NULL , 8>      ProductID int NOT NULL , 9>      UnitPrice money NOT NULL DEFAULT (0), 10>     Quantity smallint NOT NULL DEFAULT (1), 11>     Discount real NOT NULL DEFAULT (0) 12> ) 13> GO 1> INSERT OrderDetails VALUES(10248,11,14,12,0) 2> INSERT OrderDetails VALUES(10248,42,9.8,10,0) 3> INSERT OrderDetails VALUES(10248,72,34.8,5,0) 4> INSERT OrderDetails VALUES(10249,14,18.6,9,0) 5> INSERT OrderDetails VALUES(10249,51,42.4,40,0) 6> INSERT OrderDetails VALUES(10250,41,7.7,10,0) 7> INSERT OrderDetails VALUES(10250,51,42.4,35,0.15) 8> INSERT OrderDetails VALUES(10250,65,16.8,15,0.15) 9> INSERT OrderDetails VALUES(10251,22,16.8,6,0.05) 10> INSERT OrderDetails VALUES(10251,57,15.6,15,0.05) 11> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3>    DECLARE @Test money 4> 5>    SET @Test = (SELECT MAX(UnitPrice) FROM OrderDetails) 6>    SELECT @Test 7> GO ---------------------               42.4000 (1 rows affected) 1> 2> drop table OrderDetails; 3> GO