I have two variables, one is called PaidThisMonth, and the other is called OwedPast. They are both results of some subqueries in SQL. How can I select the smaller of the two and return it as a value titled PaidForPast?
The MIN function works on columns, not variables.
SQL Server 2012 and 2014 supports IIF(cont,true,false) function. Thus for minimal selection you can use it like
SELECT IIF(first>second, second, first) the_minimal FROM table
While IIF is just a shorthand for writing CASE...WHEN...ELSE, it's easier to write.
IIF is just a syntactic sugar for CASE...WHEN...ELSE.
CASE...WHEN...ELSE is so awkward to read. IIF is way clearer and shorter.
The solutions using CASE, IIF, and UDF are adequate, but impractical when extending the problem to the general case using more than 2 comparison values. The generalized solution in SQL Server 2008+ utilizes a strange application of the VALUES clause:
SELECT
PaidForPast=(SELECT MIN(x) FROM (VALUES (PaidThisMonth),(OwedPast)) AS value(x))
Credit due to this website: http://sqlblog.com/blogs/jamie_thomson/archive/2012/01/20/use-values-clause-to-get-the-maximum-value-from-some-columns-sql-server-t-sql.aspx
MIN(x*(case x when 0 then null else 1 end))
Use Case:
Select Case When @PaidThisMonth < @OwedPast
Then @PaidThisMonth Else @OwedPast End PaidForPast
As Inline table valued UDF
CREATE FUNCTION Minimum
(@Param1 Integer, @Param2 Integer)
Returns Table As
Return(Select Case When @Param1 < @Param2
Then @Param1 Else @Param2 End MinValue)
Usage:
Select MinValue as PaidforPast
From dbo.Minimum(@PaidThisMonth, @OwedPast)
ADDENDUM: This is probably best for when addressing only two possible values, if there are more than two, consider Craig's answer using Values clause.
For SQL Server 2022+ (or MySQL or PostgreSQL 9.3+), a better way is to use the LEAST and GREATEST functions.
SELECT GREATEST(A.date0, B.date0) AS date0,
LEAST(A.date1, B.date1, B.date2) AS date1
FROM A, B
WHERE B.x = A.x
With:
GREATEST(value [, ...]) : Returns the largest (maximum-valued) argument from values providedLEAST(value [, ...]) Returns the smallest (minimum-valued) argument from values providedDocumentation links :
GREATEST and LEAST are promised to be available in Sql2022, so this answer was just over 6 years too early. ;)I just had a situation where I had to find the max of 4 complex selects within an update. With this approach you can have as many as you like!
You can also replace the numbers with aditional selects
select max(x)
from (
select 1 as 'x' union
select 4 as 'x' union
select 3 as 'x' union
select 2 as 'x'
) a
More complex usage
@answer = select Max(x)
from (
select @NumberA as 'x' union
select @NumberB as 'x' union
select @NumberC as 'x' union
select (
Select Max(score) from TopScores
) as 'x'
) a
I'm sure a UDF has better performance.
Here is a trick if you want to calculate maximum(field, 0):
SELECT (ABS(field) + field)/2 FROM Table
returns 0 if field is negative, else, return field.
SELECT @a - ( ABS(@a-@b) + (@a-@b) ) / 2
Use a CASE statement.
Example B in this page should be close to what you're trying to do:
http://msdn.microsoft.com/en-us/library/ms181765.aspx
Here's the code from the page:
USE AdventureWorks; GO SELECT ProductNumber, Name, 'Price Range' = CASE WHEN ListPrice = 0 THEN 'Mfg item - not for resale' WHEN ListPrice < 50 THEN 'Under $50' WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250' WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000' ELSE 'Over $1000' END FROM Production.Product ORDER BY ProductNumber ; GO
Select MIN(T.V) FROM (Select 1 as V UNION Select 2 as V) T
Building on the brilliant logic / code from mathematix and scottyc, I submit:
DECLARE @a INT, @b INT, @c INT = 0;
WHILE @c < 100
BEGIN
SET @c += 1;
SET @a = ROUND(RAND()*100,0)-50;
SET @b = ROUND(RAND()*100,0)-50;
SELECT @a AS a, @b AS b,
@a - ( ABS(@a-@b) + (@a-@b) ) / 2 AS MINab,
@a + ( ABS(@b-@a) + (@b-@a) ) / 2 AS MAXab,
CASE WHEN (@a <= @b AND @a = @a - ( ABS(@a-@b) + (@a-@b) ) / 2)
OR (@a >= @b AND @a = @a + ( ABS(@b-@a) + (@b-@a) ) / 2)
THEN 'Success' ELSE 'Failure' END AS Status;
END;
Although the jump from scottyc's MIN function to the MAX function should have been obvious to me, it wasn't, so I've solved for it and included it here: SELECT @a + ( ABS(@b-@a) + (@b-@a) ) / 2. The randomly generated numbers, while not proof, should at least convince skeptics that both formulae are correct.
This works for up to 5 dates and handles nulls. Just couldn't get it to work as an Inline function.
CREATE FUNCTION dbo.MinDate(@Date1 datetime = Null,
@Date2 datetime = Null,
@Date3 datetime = Null,
@Date4 datetime = Null,
@Date5 datetime = Null)
RETURNS Datetime AS
BEGIN
--USAGE select dbo.MinDate('20120405',null,null,'20110305',null)
DECLARE @Output datetime;
WITH Datelist_CTE(DT)
AS (
SELECT @Date1 AS DT WHERE @Date1 is not NULL UNION
SELECT @Date2 AS DT WHERE @Date2 is not NULL UNION
SELECT @Date3 AS DT WHERE @Date3 is not NULL UNION
SELECT @Date4 AS DT WHERE @Date4 is not NULL UNION
SELECT @Date5 AS DT WHERE @Date5 is not NULL
)
Select @Output=Min(DT) FROM Datelist_CTE;
RETURN @Output;
END;
Use a temp table to insert the range of values, then select the min/max of the temp table from within a stored procedure or UDF. This is a basic construct, so feel free to revise as needed.
For example:
CREATE PROCEDURE GetMinSpeed() AS
BEGIN
CREATE TABLE #speed (Driver NVARCHAR(10), SPEED INT);
'
' Insert any number of data you need to sort and pull from
'
INSERT INTO #speed (N'Petty', 165)
INSERT INTO #speed (N'Earnhardt', 172)
INSERT INTO #speed (N'Patrick', 174)
SELECT MIN(SPEED) FROM #speed
DROP TABLE #speed
END
() is not a valid parameter list. To declare a procedure with no parameters, it seems you need to not have brackets at all. * ' is not a comment marker in SQL. * You're missing the VALUES keyword. * You're missing the semicolons. It's important not to leave these off: stackoverflow.com/questions/710683/…SELECT (WHEN first > second THEN second ELSE first END) the_minimal FROM table
CASE keyword. And if made valid syntax then repeats existing answers