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?
First of all, nice question. I was remembered of the fact that OVER() can also be used with other functions except ranking functions.
To answer tilews question: most of the time (in my case that is :-)), OVER() and partition by is used with ranking functions.
An example:
SELECT * FROM
(SELECT
column1
,column2
...
,
ROW_NUMBER() OVER(PARTITION BY myBusinessKey ORDER BY Priority DESC) AS RID
FROM myTable) tmp
WHERE RID = 1
This code will select all the rows from a table, partition them by business key, order it by some column that designates a priority (doesn't have to be, but just for the sake of the example) and then selects every row with a row_number of 1. This will select all the rows with unique values of the business key and with the highest priority (thus returning no duplicates).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP