[SOLUCIONADO] Uso de max y min en SQL Sever

[KATHYU]

Buenas señores estoy trabajando con la base de pruebas de microsoft y pues tengo la sigueinte consulta en sql:

Código SQL:

SELECT top 25 orders.orderid AS id,customers.companyname AS cliente,employees.firstname AS vendedor,shippers.companyname 
AS via_entrega,orders.orderdate AS fecha_compra,orders.freight AS valor_compra FROM Orders INNER JOIN 
customers ON orders.customerid = customers.customerid INNER JOIN employees ON orders.employeeid = 
employees.employeeid INNER JOIN shippers ON orders.shipvia = shippers.shipperid
 

El resultado es el deseado, pero si uso max para sacar cual es el la compra de maximo valor o minimo no me la da, ya he probado de muchas formas y pos no me sale

Código SQL:

SELECT top 25 orders.orderid AS id,customers.companyname AS cliente,employees.firstname AS vendedor,shippers.companyname 
AS via_entrega,orders.orderdate AS fecha_compra,MAX (orders.freight AS valor_compra) FROM Orders INNER JOIN 
customers ON orders.customerid = customers.customerid INNER JOIN employees ON orders.employeeid = 
employees.employeeid INNER JOIN shippers ON orders.shipvia = shippers.shipperid
 

Sera que estoy usando mal el termino ?? lo he usado de otras formas asi como sale en los manuales y no sale, alguien me ayuda por fissss

[GeriReshef]

Proba esto (con Over() despues del Max()):

Código SQL:

SELECT top 25 orders.orderid AS id,customers.companyname AS cliente,employees.firstname AS vendedor,shippers.companyname 
AS via_entrega,orders.orderdate AS fecha_compra,MAX (orders.freight AS valor_compra) OVER() FROM Orders INNER JOIN 
customers ON orders.customerid = customers.customerid INNER JOIN employees ON orders.employeeid = 
employees.employeeid INNER JOIN shippers ON orders.shipvia = shippers.shipperid;
 

[KATHYU]

Iniciado por GeriReshef

Proba esto (con Over() despues del Max()):

Código SQL:

SELECT top 25 orders.orderid AS id,customers.companyname AS cliente,employees.firstname AS vendedor,shippers.companyname 
AS via_entrega,orders.orderdate AS fecha_compra,MAX (orders.freight AS valor_compra) OVER() FROM Orders INNER JOIN 
customers ON orders.customerid = customers.customerid INNER JOIN employees ON orders.employeeid = 
employees.employeeid INNER JOIN shippers ON orders.shipvia = shippers.shipperid;
 

No funciona ya lo habia intentado

Error: Mens. 195, Nivel 15, Estado 10, Línea 2
'MAX' is not a recognized built-in function name.

Pero cambie el orden de las cosas:

Código SQL:

SELECT top 25 orders.orderid AS id,customers.companyname AS cliente,employees.firstname AS vendedor,shippers.companyname 
AS via_entrega,orders.orderdate AS fecha_compra,MAX (orders.freight) OVER() AS maxima FROM Orders INNER JOIN 
customers ON orders.customerid = customers.customerid INNER JOIN employees ON orders.employeeid = 
employees.employeeid INNER JOIN shippers ON orders.shipvia = shippers.shipperid
 

Y ahi si da, la cosa es poner el alias hasta despues del over ()

Gracias !!!!