Hugo Kornelis (11/4/2010)

---

(If you take away the knowledge that you can use the OVER() clause for all aggregates except CHECKPOINT, that's fine. But if you intend to use this query for similar purposes, think again. There are far better ways to get the maximum tax rate per state/province id:

Select StateProvinceID,
         TaxType,
         Max(TaxRate) Over (Partition by StateProvinceID),
         Max(TaxRate) Over (Partition by StateProvinceID, TaxType)
from Sales.SalesTaxRate
Order by StateProvinceID

mislamuddin (11/3/2010)

---

Hi:
If select distinct specified order by item must appear in the select list and there will be error message as follows:
Msg 145, Line 15 St 1

Hugo
If you take away the knowledge that you can use the OVER() clause for all aggregates except CHECKPOINT, that's fine. But if you intend to use this query for similar purposes, think again. There are far better ways to get the maximum tax rate per state/province id:

brdudley
One other important take away, although not illustrated in this question, is that a Select can have multiple Over clauses with different Partitions. This is something that Group By cannot do.

tilew-948340 (11/4/2010)

---

My engish beeing what it is, I did also not get the differance between the first two answers, but that's ok. I learned something new and it is the important thing. Thank you!
I am not sure of what "over" does thought...and yes, I was starting to thing that it does the same thing than a group by.
So, what would be the first purpose of using an Over(partition by) function?

SELECT * FROM
	(SELECT
		 column1
		,column2
		...
		,
		ROW_NUMBER() OVER(PARTITION BY myBusinessKey ORDER BY Priority DESC) AS RID
	FROM myTable) tmp
WHERE RID = 1

tilew-948340 (11/4/2010)

---

My engish beeing what it is, I did also not get the differance between the first two answers, but that's ok. I learned something new and it is the important thing. Thank you!
I am not sure of what "over" does thought...and yes, I was starting to thing that it does the same thing than a group by.
So, what would be the first purpose of using an Over(partition by) function?