sql server 2008 - Group by excluding Null - tsql -

sql server 2008 - Group by excluding Null - tsql -

i have script below:

;;with cte ( select rank() on (partition portfolioid order sum(percentage) desc,max(securityname)) [rank] , reportingdate , portfolioid , portfolionme , max(securityname) securityname , cast(sum(percentage) decimal(22,1)) [weight] , sedol , max(isin) isin @worktable wt wt.issuetype2 <> '010' , wt.issuetype2 <> '055' , wt.issuetype1 <> '110' -- remove cash , fx , collateral grouping wt.reportingdate , wt.portfolioid , wt.portfolionme , wt.sedol ) select convert(varchar, reportingdate, 103) reportingdate , portfolioid fundcode , portfolionme fundname , securityname instrumentname , [rank] , [weight] percentage , sedol , isin cte [rank] <= 10 order reportingdate, portfolioid, [rank], [weight] desc

i'm grouping sedol want grouping same sedols together, causing nulls grouping together. i've tried changing script adding max around sedol , putting:

case when sedol null securityname else sedol end

in grouping no success.

any help much appreciated.

thanks

how about:

;;with cte ( select rank() on (partition portfolioid order sum(percentage) desc,max(securityname)) [rank] , reportingdate , portfolioid , portfolionme , max(securityname) securityname , cast(sum(percentage) decimal(22,1)) [weight] , isnull(sedol, securityname) sedol , max(isin) isin @worktable wt wt.issuetype2 <> '010' , wt.issuetype2 <> '055' , wt.issuetype1 <> '110' -- remove cash , fx , collateral grouping wt.reportingdate , wt.portfolioid , wt.portfolionme , isnull(wt.sedol, securityname) ) select convert(varchar, reportingdate, 103) reportingdate , portfolioid fundcode , portfolionme fundname , securityname instrumentname , [rank] , [weight] percentage , sedol , isin cte [rank] <= 10 order reportingdate, portfolioid, [rank], [weight] desc

sql-server-2008 tsql group-by common-table-expression