My question is WHY is that, in my understanding
TOP looks at the data same as
SELECT column FROM table and when you do a
ORDER BY column it sorts the results and than gives you only the top row, while the
MAX() functions should really just look at each row check the sum of that row, then go to the next row and if that row is
< the previous row than it saves the results in the function depends if it's a
MAX() or a
MIN(), and this is exactly what the
ORDER BY should do.
Please don't answer that in your experience the
MAX() is better than
TOP as the question is really what's the difference between
TOP? both look at the data on each row and both are probably a log(n).
In response to @J.D:
One simple example is if you use TOP without an ORDER BY clause. Then the top result you'll get back is likely non-deterministic and doesn't always have to be the same row that the MAX() aggregate function would reference.
Absolutely I'm only asking when you are using
ORDER BY because the powerfulness
TOP has is when sorting it in a specific way.
Regarding the log(n) I would ask it in a different way, if the
ORDER BY has a Ω(n) because the minimum data it needs to look on is the n and has probably a O() >= n because it's probably using the best sorting algorithm which O(n).
MIN() it's also probably a Ω(n) and O(n) because it will always need to scan all the data to find the answer, and never more than n, so why does it sometimes make a huge difference?
To put it another way:
What's the difference in execution time if I have a table of a million rows that has customers and the amount they've spent to buy product's, if I'm using
SELECT TOP 1 amount_column FROM customer_table ORDER BY amount_column DESC and using
SELECT MAX(amount_column) FROM customer_table the first need's to look at all the data in
amount_column and sort it descending order so it doesn't make a difference if it's already sorted or not because it needs to check all rows if it's > than previous row, and then when it finished executing it returns the maximum amount.
And when I'm doing it using the
MAX() function it also needs to scan (in my understanding) all rows and save the first row to the results and then go to the next row and check if it's > than results if yes it saves it to results, and so on for the whole table, and when it finishes it returns the maximum amount.
So in my understanding both are really doing the same thing, or maybe I'm wrong with my understanding the way it executes the function, and that's what I'm asking if I'm wrong? and why?