top 10 by grouping - 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

top 10 by grouping

Author

Topic

gavakie
Posting Yak Master

221 Posts

Posted - 2009-11-16 : 16:25:58

I need to show a top 10 in this queries case for each store.
So ID store 1 would have its top 10 records, store 2 its top 10 records and so on.

Select
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

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-16 : 16:29:09

TOP 10 ORDER BY what?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam