Hay una función Max en SQL Server que toma dos valores como Math.Max in.NET?
Quiero escribir una consulta como esta:
SELECT o.OrderId, MAX(o.NegotiatedPrice, o.SuggestedPrice)
FROM Order o
Pero así no es como funciona la función MAX
, ¿verdad? Es una función agregada por lo que espera un solo parámetro y luego devuelve el MÁXIMO de todas las filas.
¿alguien sabe cómo hacerlo a mi manera?
25 answers
Necesitarías hacer un User-Defined Function
si quisieras tener una sintaxis similar a tu ejemplo, pero podrías hacer lo que quieres hacer, en línea, bastante fácilmente con una sentencia CASE
, como han dicho los demás.
El UDF
podría ser algo así:
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-05-15 18:23:32
Si está utilizando SQL Server 2008 (o superior), entonces 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 of multiple columns?"
Digo que es la "mejor respuesta " porque:
- No requiere complicar su código con UNION's, PIVOT's, UNPIVOT's, UDF's, and crazy-long CASE statments.
- No está plagado con el problema de manejar los nulos, solo los maneja fino.
- Es fácil cambiar el "MAX" por "MIN", "AVG" o "SUM". Puede usar cualquier función de agregado para encontrar el agregado en muchas columnas diferentes.
- No estás limitado a los nombres que usé (es decir, "AllPrices" y "Price"). Usted puede elegir sus propios nombres para que sea más fácil de leer y entender para el próximo chico.
- Puede encontrar varios agregados usando derived_tables de SQL Server 2008 de la siguiente manera:
SELECCIONE MAX(a), MAX (b) DE (VALORES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) COMO MyTable (a, b)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:10:43
Se puede hacer en una línea:
-- the following expression calculates ==> max(@val1, @val2)
SELECT 0.5 * ((@val1 + @val2) + ABS(@val1 - @val2))
Editar: Si está tratando con números muy grandes, tendrá que convertir las variables de valor en bigint para evitar un desbordamiento de enteros.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-07-24 06:02:26
No lo creo. Quería esto el otro día. Lo más cercano que tuve fue:
SELECT
o.OrderId,
CASE WHEN o.NegotiatedPrice > o.SuggestedPrice THEN o.NegotiatedPrice
ELSE o.SuggestedPrice
END
FROM Order o
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-09-23 22:56:15
¿Por qué no probar IIF función (requiere SQL Server 2012 y posterior)
IIF(a>b, a, b)
Eso es todo.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-02-02 23:14:12
DECLARE @MAX INT
@MAX = (SELECT MAX(VALUE)
FROM (SELECT 1 AS VALUE UNION
SELECT 2 AS VALUE) AS T1)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-09-25 19:54:16
Las otras respuestas son buenas, pero si tiene que preocuparse por tener valores NULOS, puede 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 sub consultas pueden acceder a las columnas desde la consulta externa, por lo que puede usar este enfoque para usar agregados como MAX
entre columnas. (Probablemente más útil cuando hay un mayor número de columnas involucradas sin embargo)
;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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-10-21 15:53:22
Presentación de SQL Server 2012IIF
:
SELECT
o.OrderId,
IIF( ISNULL( o.NegotiatedPrice, 0 ) > ISNULL( o.SuggestedPrice, 0 ),
o.NegotiatedPrice,
o.SuggestedPrice
)
FROM
Order o
Se recomienda manejar NULLs cuando se usa IIF
, porque un NULL
a cada lado de su boolean_expression
hará que IIF
devuelva el false_value
(en lugar de NULL
).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-06-26 17:09:52
Yo iría con la solución proporcionada por kcrumley Simplemente modifíquelo 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 del comentario de Mark. Como señaló correctamente en la lógica de 3 valores x > NULL o x
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 11:55:09
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-02-24 15:32:17
Oops, acabo de publicar un dupe de esta pregunta...
La respuesta es, no hay una función incorporada como El mayor de Oracle, pero puede lograr un resultado similar para 2 columnas con un UDF, tenga en cuenta que el uso de sql_variant es bastante 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
Publicó 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 11:47:26
Aquí hay un ejemplo de caso que debería manejar nulls y funcionará con versiones anteriores de MSSQL. Esto se basa en la función inline en uno de los ejemplos populares:
case
when a >= b then a
else isnull(b,a)
end
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-10-24 14:35:00
Probablemente no lo haría de esta manera, ya que es menos eficiente que las construcciones de CASOS ya mencionadas, a menos que, tal vez, tenga índices de cobertura 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-09-23 23:02:26
Aquí hay una versión IIF con manejo NULO (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 cualquiera de los valores es NULL, devuelve el que no es 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))
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-09-06 11:55:00
Puedes hacer algo como esto:
select case when o.NegotiatedPrice > o.SuggestedPrice
then o.NegotiatedPrice
else o.SuggestedPrice
end
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-09-23 22:57:18
SELECT o.OrderID
CASE WHEN o.NegotiatedPrice > o.SuggestedPrice THEN
o.NegotiatedPrice
ELSE
o.SuggestedPrice
END AS Price
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-09-23 22:57:20
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-06-07 20:11:48
Para la respuesta anterior sobre números grandes, puede hacer la multiplicación antes de la suma/resta. Es un poco más voluminoso, pero no requiere yeso. (No puedo hablar de velocidad, pero supongo que todavía es bastante rápido)
SELECCIONE 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 desea evitar el casting.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-10-21 15:46:23
En su forma más simple...
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-10-17 12:46:27
Para SQL Server 2012:
SELECT
o.OrderId,
IIF( o.NegotiatedPrice >= o.SuggestedPrice,
o.NegotiatedPrice,
ISNULL(o.SuggestedPrice, o.NegiatedPrice)
)
FROM
Order o
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-12-07 12:45:35
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-09-12 13:14:01
Aquí está la respuesta de @Scott Langham con un manejo sencillo de 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-04-20 15:16:38
select OrderId, (
select max([Price]) from (
select NegotiatedPrice [Price]
union all
select SuggestedPrice
) p
) from [Order]
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-10-04 14:48:42
En Presto podrías usar use
SELECT array_max(ARRAY[o.NegotiatedPrice, o.SuggestedPrice])
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-11-13 00:27:23