So I knew about over but didn't have the opportunity to use it every day. But today, I needed a query that would group the data by two criteria; for each group I have a number and then for each group I need the row with that number to be the MAX in the group by first criteria.
Like this: I have table T with Columns A, B and theNumber and I do a
SELECT A,b, sum(theNumber)
FROM T
GROUP BY A, B
And from this I need the rows with the max sum(theNumber) per A
That resulted into
SELECT A, min(B), theNb FROM (
SELECT A, B, theNb, MAX(theNb) OVER (PARTITION BY A) maxim
FROM (
SELECT A,B, sum(thenumber) as theNb FROM T
WHERE someCondition
GROUP BY A, B) aa
) bb
WHERE theNb= maxim
instead of big subQueries and clumsy code
