How could I get the maximun value on a field on SQL Server without max?

Question

I need to get the max value on a field of a table but I can't use max or any other aggregation function nor cursors. For example I need to get the max value of the field amount on the table Sales.

example : SELECT TOP 1 amount FROM Sales ORDER BY amount DESC — iSR5
– iSR5, Commented Jul 22, 2019 at 22:23
Thanks for your answer, but I can't use aggregation nor top functions — Luis Diego Arias
– Luis Diego Arias, Commented Jul 22, 2019 at 22:26
Please add this constraint to your original question. I take it this is not an actual problem, it's just an experiment or a rhetorical question — Nick.Mc
– Nick.Mc, Commented Jul 22, 2019 at 22:34

forpas · Accepted Answer · 2019-07-22 22:28:20Z

1

A couple of ways:
1. Sort the column descending and get the 1st row:

select top 1 amount from sales order by amount DESC

2. With NOT EXISTS:

select distinct s.amount 
from sales s 
where not exists (
  select 1 from sales
  where amount > s.amount
)

answered Jul 22, 2019 at 22:28

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luis Diego Arias Over a year ago

It Works, Thanks a lot!

Collectives™ on Stack Overflow

How could I get the maximun value on a field on SQL Server without max?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related