Wednesday, January 14, 2009

Don't get over OVER

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

No comments: