two - ¿Existe una función Max en SQL Server que toma dos valores como Math.Max en.NET?
sql server least (25)
¿Por qué no probar la función IIF (requiere SQL Server 2012 y versiones posteriores)?
IIF(a>b, a, b)
Eso es.
Quiero escribir una consulta como esta:
SELECT o.OrderId, MAX(o.NegotiatedPrice, o.SuggestedPrice)
FROM Order o
Pero no es así como funciona la función MAX
, ¿verdad? Es una función agregada, por lo que espera un solo parámetro y luego devuelve el MAX de todas las filas.
¿Alguien sabe cómo hacerlo a mi manera?
Aquí está la respuesta de @Scott Langham con un sencillo manejo NULL:
SELECT
o.OrderId,
CASE WHEN (o.NegotiatedPrice > o.SuggestedPrice OR o.SuggestedPrice IS NULL)
THEN o.NegotiatedPrice
ELSE o.SuggestedPrice
END As MaxPrice
FROM Order o
Aquí hay un ejemplo de caso que debería manejar nulos y funcionará con versiones anteriores de MSSQL. Esto se basa en la función en línea en uno de los ejemplos populares:
case
when a >= b then a
else isnull(b,a)
end
Aquí hay una versión IIF con manejo NULL (basado en la respuesta de Xin):
IIF(a IS NULL OR b IS NULL, ISNULL(a,b), IIF(a > b, a, b))
La lógica es la siguiente, si alguno de los valores es NULL, devuelva el que no sea NULL (si ambos son NULL, se devuelve un NULL). De lo contrario devuelve el mayor.
Lo mismo se puede hacer para MIN.
IIF(a IS NULL OR b IS NULL, ISNULL(a,b), IIF(a < b, a, b))
En Presto podrias usarlo.
SELECT array_max(ARRAY[o.NegotiatedPrice, o.SuggestedPrice])
En su forma más simple ...
SELECT
o.OrderId,
IIF( o.NegotiatedPrice >= o.SuggestedPrice,
o.NegotiatedPrice,
ISNULL(o.SuggestedPrice, o.NegiatedPrice)
)
FROM
Order o
Es tan simple como esto:
CREATE FUNCTION InlineMax
(
@p1 sql_variant,
@p2 sql_variant
) RETURNS sql_variant
AS
BEGIN
RETURN CASE
WHEN @p1 IS NULL AND @p2 IS NOT NULL THEN @p2
WHEN @p2 IS NULL AND @p1 IS NOT NULL THEN @p1
WHEN @p1 > @p2 THEN @p1
ELSE @p2 END
END;
Las otras respuestas son buenas, pero si tiene que preocuparse por tener valores NULL, es posible que desee esta variante:
SELECT o.OrderId,
CASE WHEN ISNULL(o.NegotiatedPrice, o.SuggestedPrice) > ISNULL(o.SuggestedPrice, o.NegotiatedPrice)
THEN ISNULL(o.NegotiatedPrice, o.SuggestedPrice)
ELSE ISNULL(o.SuggestedPrice, o.NegotiatedPrice)
END
FROM Order o
Las subconsultas pueden acceder a las columnas desde la consulta externa, por lo que puede utilizar este enfoque para usar agregados, como MAX
en las columnas. (Aunque probablemente sea más útil cuando hay un mayor número de columnas involucradas)
;WITH [Order] AS
(
SELECT 1 AS OrderId, 100 AS NegotiatedPrice, 110 AS SuggestedPrice UNION ALL
SELECT 2 AS OrderId, 1000 AS NegotiatedPrice, 50 AS SuggestedPrice
)
SELECT
o.OrderId,
(SELECT MAX(price)FROM
(SELECT o.NegotiatedPrice AS price
UNION ALL SELECT o.SuggestedPrice) d)
AS MaxPrice
FROM [Order] o
Me gustaría ir con la solución provista por kcrumley Solo kcrumley ligeramente para manejar NULLs
create function dbo.HigherArgumentOrNull(@val1 int, @val2 int)
returns int
as
begin
if @val1 >= @val2
return @val1
if @val1 < @val2
return @val2
return NULL
end
EDITAR Modificado después de un comentario de Mark . Como lo señaló correctamente en la lógica de 3 valores x> NULL o x <NULL siempre debe devolver NULL. En otras palabras, resultado desconocido.
Necesitaría crear una User-Defined Function
si quisiera tener una sintaxis similar a su ejemplo, pero podría hacer lo que quiera hacer, en línea, con bastante facilidad con una declaración CASE
, como han dicho los demás.
La UDF
podría ser algo como esto:
create function dbo.InlineMax(@val1 int, @val2 int)
returns int
as
begin
if @val1 > @val2
return @val1
return isnull(@val2,@val1)
end
... y lo llamarías así ...
SELECT o.OrderId, dbo.InlineMax(o.NegotiatedPrice, o.SuggestedPrice)
FROM Order o
No lo creo. Quería esto el otro día. Lo más cerca que conseguí fue:
SELECT
o.OrderId,
CASE WHEN o.NegotiatedPrice > o.SuggestedPrice THEN o.NegotiatedPrice
ELSE o.SuggestedPrice
END
FROM Order o
Para SQL Server 2012:
SELECT o.OrderId,
--MAX(o.NegotiatedPrice, o.SuggestedPrice)
(SELECT MAX(v) FROM (VALUES (o.NegotiatedPrice), (o.SuggestedPrice)) AS value(v)) as ChoosenPrice
FROM Order o
Para la respuesta anterior con respecto a los números grandes, puedes hacer la multiplicación antes de la suma / resta. Es un poco más voluminoso, pero no requiere reparto. (No puedo hablar de velocidad, pero supongo que todavía es bastante rápido)
SELECCIONA 0.5 * ((@ val1 + @ val2) + ABS (@ val1 - @ val2))
Cambios a
SELECCIONE @ val1 * 0.5 + @ val2 * 0.5 + ABS (@ val1 * 0.5 - @ val2 * 0.5)
Al menos una alternativa si quieres evitar el casting.
Probablemente no lo haría de esta manera, ya que es menos eficiente que las construcciones CASE ya mencionadas, a menos que, tal vez, haya cubierto índices para ambas consultas. De cualquier manera, es una técnica útil para problemas similares:
SELECT OrderId, MAX(Price) as Price FROM (
SELECT o.OrderId, o.NegotiatedPrice as Price FROM Order o
UNION ALL
SELECT o.OrderId, o.SuggestedPrice as Price FROM Order o
) as A
GROUP BY OrderId
Puedes hacer algo como esto:
SELECT o.OrderID
CASE WHEN o.NegotiatedPrice > o.SuggestedPrice THEN
o.NegotiatedPrice
ELSE
o.SuggestedPrice
END AS Price
SQL Server 2012 introdujo IIF
:
SELECT
o.OrderId,
IIF( ISNULL( o.NegotiatedPrice, 0 ) > ISNULL( o.SuggestedPrice, 0 ),
o.NegotiatedPrice,
o.SuggestedPrice
)
FROM
Order o
Se recomienda el manejo de NULL al usar IIF
, porque un NULL
a cada lado de su boolean_expression
hará que IIF
devuelva el false_value
(en lugar de NULL
).
Se puede hacer en una sola línea:
-- the following expression calculates ==> max(@val1, @val2)
SELECT 0.5 * ((@val1 + @val2) + ABS(@val1 - @val2))
Edición: si se trata de números muy grandes, tendrá que convertir las variables de valor en bigint para evitar un desbordamiento de enteros.
Si está utilizando SQL Server 2008 (o superior), esta es la mejor solución:
SELECT o.OrderId,
(SELECT MAX(Price)
FROM (VALUES (o.NegotiatedPrice),(o.SuggestedPrice)) AS AllPrices(Price))
FROM Order o
Todo el crédito y los votos deben ir a la respuesta de Sven a una pregunta relacionada, "¿SQL MAX de columnas múltiples?"
Yo digo que es la " mejor respuesta " porque:
- No es necesario que complique su código con las declaraciones de CASE de UNION, PIVOT, UNPIVOT, UDF y CAST.
- No está plagado con el problema de manejar nulos, los maneja bien.
- Es fácil cambiar el "MAX" por "MIN", "AVG" o "SUM". Puede usar cualquier función agregada para encontrar el agregado en muchas columnas diferentes.
- No está limitado a los nombres que utilicé (es decir, "Todos los precios" y "Precio"). Puedes elegir tus propios nombres para que sea más fácil de leer y entender para el próximo chico.
- Puede encontrar múltiples agregados utilizando las derived_tables SQL Server 2008 de derived_tables manera:
SELECCIONE MAX (a), MAX (b) DE (VALORES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10)) COMO MyTable (a, b)
Vaya, acabo de publicar un duplicado de esta pregunta ...
La respuesta es que no hay una función integrada como Oracle''s Greatest , pero puede lograr un resultado similar para 2 columnas con un UDF, tenga en cuenta que el uso de sql_variant es muy importante aquí.
create table #t (a int, b int)
insert #t
select 1,2 union all
select 3,4 union all
select 5,2
-- option 1 - A case statement
select case when a > b then a else b end
from #t
-- option 2 - A union statement
select a from #t where a >= b
union all
select b from #t where b > a
-- option 3 - A udf
create function dbo.GREATEST
(
@a as sql_variant,
@b as sql_variant
)
returns sql_variant
begin
declare @max sql_variant
if @a is null or @b is null return null
if @b > @a return @b
return @a
end
select dbo.GREATEST(a,b)
from #t
kristof
Publicado esta respuesta:
create table #t (id int IDENTITY(1,1), a int, b int)
insert #t
select 1,2 union all
select 3,4 union all
select 5,2
select id, max(val)
from #t
unpivot (val for col in (a, b)) as unpvt
group by id
select OrderId, (
select max([Price]) from (
select NegotiatedPrice [Price]
union all
select SuggestedPrice
) p
) from [Order]
CREATE FUNCTION [dbo].[fnMax] (@p1 INT, @p2 INT)
RETURNS INT
AS BEGIN
DECLARE @Result INT
SET @p2 = COALESCE(@p2, @p1)
SELECT
@Result = (
SELECT
CASE WHEN @p1 > @p2 THEN @p1
ELSE @p2
END
)
RETURN @Result
END
CREATE FUNCTION fnGreatestInt (@Int1 int, @Int2 int )
RETURNS int
AS
BEGIN
IF @Int1 >= ISNULL(@Int2,@Int1)
RETURN @Int1
ELSE
RETURN @Int2
RETURN NULL --Never Hit
END
DECLARE @MAX INT
@MAX = (SELECT MAX(VALUE)
FROM (SELECT 1 AS VALUE UNION
SELECT 2 AS VALUE) AS T1)
select case when o.NegotiatedPrice > o.SuggestedPrice
then o.NegotiatedPrice
else o.SuggestedPrice
end