rank ties - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

rank ties

Author

Topic

gavakie
Posting Yak Master

221 Posts

Posted - 2009-11-17 : 13:06:06

I have this query here some of the ranks are tied at ten and I want to use Basehousehold count as the tie breaker so it will only display when tied the one with the larger basehouseholdcount.

Select *
From
(Select
rank() over(partition by #ttMain.lngStoreID order by (CAST(Round((case when BaseHouseHoldCount = 0 then 0 else (ACCount/ BaseHouseHoldCount)end/ Case when TotalCustomercount = 0 then 0 else (TotalCustomerCount/TotalBlockSize)end)*100,0) as int)) desc) as rnk,
CAST(Round((case when BaseHouseHoldCount = 0 then 0 else (ACCount/ BaseHouseHoldCount)end/ Case when TotalCustomercount = 0 then 0 else (TotalCustomerCount/TotalBlockSize)end)*100,0) as int) ACIndex,
#ttMain.strMosaicCode,
#ttMain.lngStoreID,
strStoreName,
CAST(Round((case when HVRCount = 0 then 0 else (HVRCount/ACCount)end /Case when TotalCustomerCount = 0 then 0 else(TotalCustomerCount/TotalBlockSize)end)*100,0) as INT)HVIndex,
ACCount/TotalCustomerCount as 'Comp per',
BaseHouseHoldCount
From #ttMain
Left Join #ttTotal
on #ttMain.lngStoreID = #ttTotal.TotalStoreID
Left Join #ttGroupTotal
on left(#ttMain.strMosaicCode,1) = #ttGroupTotal.designation
and #ttMain.lngStoreID = #ttGroupTotal.GroupStoreId
Inner Join (SELECT s.lngStoreId, CAST(si.txtStoreNotes as varchar(4)) + ' - ' + SUBSTRING(strStoreName, LEN(strStoreName) - 2, 3) + ' - ' + SUBSTRING(strStoreName, 0, LEN(strStoreName) - 2) as strStoreName
FROM tblStore s
JOIN tblStoreInfo si
ON s.lngStoreId = si.lngStoreId
WHERE s.blnIsActive = 1

)ttStore
ON #ttMain.lngStoreID = ttStore.lngStoreId
)ttTemp
Where rnk <= 10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources