SQLServerCentral.com / SQL Server 2008 - General / SQL Server 2008 / Selecting TOP PERCENT based on GROUP ? / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 18:42:18 GMT20RE: Selecting TOP PERCENT based on GROUP ?http://www.sqlservercentral.com/Forums/Topic1232636-391-1.aspx[quote][b]SQL_By_Chance (1/10/2012)[/b][hr]G,your query has 2 input tables but if you check CTE it is taking input from the same table. On the first thought, I too thought of using a function and applying it to outer query but that was degrading performance. Regards,Ankit[/quote]What function? My query doesn't use a function. It uses an inline derived table.As for two tables vs one, a Distinct operator on one table will be more expensive than a query of a table that has the aggregate-base properly normalized, unless we're talking about very small datasets.Wed, 11 Jan 2012 14:10:35 GMTGSquaredRE: Selecting TOP PERCENT based on GROUP ?http://www.sqlservercentral.com/Forums/Topic1232636-391-1.aspxI would also prefer an APPLY based solution. Using AdventureWorks:[code="sql"]WITH Data AS ( SELECT a.* FROM Person.Address AS a ), StateProvince AS ( SELECT DISTINCT d.StateProvinceID FROM Data AS d )SELECT Selected.AddressID, Selected.AddressLine1, Selected.AddressLine2, Selected.City, Selected.StateProvinceID, Selected.PostalCodeFROM StateProvinceCROSS APPLY( SELECT TOP (30) PERCENT d2.* FROM Data AS d2 WHERE d2.StateProvinceID = StateProvince.StateProvinceID ORDER BY d2.City) AS SelectedORDER BY StateProvince.StateProvinceID, Selected.City;[/code]This seems much clearer to me. Proper indexing might be necessary on larger input sets.Tue, 10 Jan 2012 23:34:51 GMTPaul WhiteRE: Selecting TOP PERCENT based on GROUP ?http://www.sqlservercentral.com/Forums/Topic1232636-391-1.aspxG,your query has 2 input tables but if you check CTE it is taking input from the same table. On the first thought, I too thought of using a function and applying it to outer query but that was degrading performance. Regards,AnkitTue, 10 Jan 2012 23:04:54 GMTSQL_By_ChanceRE: Selecting TOP PERCENT based on GROUP ?http://www.sqlservercentral.com/Forums/Topic1232636-391-1.aspxThe problem with that one is that, when you go back to read it a year from now, it won't be clear what it's doing and how. You can overcome that with adequate documentation, but make sure that it's in comments in the code, so it doesn't get lost.The Apply version tells you what it's doing just by reading the code. Minimal documentation needed.Tue, 10 Jan 2012 07:15:28 GMTGSquaredRE: Selecting TOP PERCENT based on GROUP ?http://www.sqlservercentral.com/Forums/Topic1232636-391-1.aspxThanks for the reply.By chance, I came across a very good blog about the same which uses the following CTE :- (http://weblogs.sqlteam.com/jeffs/archive/2008/02/21/Top-N-Percent-per-Group.aspx)with AttributebyRegion as( select addressid,statecode, cityname, rank() over (partition by cityname,statecode order by addressid desc) as AddedRank, count(*) over (partition by cityname,statecode) as RegionCount from lOCATTRIBUTE_Flagid)select distinct statecode, COUNT_State = FLOOR (COUNT(RegionCount * .30))from AttributebyRegion where AddedRank &lt;= (RegionCount * .30) group by statecodeorder by statecode-----Mon, 09 Jan 2012 10:56:16 GMTSQL_By_ChanceRE: Selecting TOP PERCENT based on GROUP ?http://www.sqlservercentral.com/Forums/Topic1232636-391-1.aspxSelect the states and cities, then use Cross Apply (or Outer Apply) to get the top 30 percent for each.Is that what you're trying to do?Something like this:[code="sql"]select state, city, locationfrom dbo.MyCitiesTablecross apply (select top 30 percent location from dbo.MyLocationsTable where MyLocationsTable.CityID = MyCitiesTable.ID order by location) as Locations ;[/code]Mon, 09 Jan 2012 10:46:40 GMTGSquaredRE: Selecting TOP PERCENT based on GROUP ?http://www.sqlservercentral.com/Forums/Topic1232636-391-1.aspx[quote][b]SQL_By_Chance (1/9/2012)[/b][hr]Hi, I wanted to know if we can select percent of records for each group?something like :- SELECT TOP 30 PERCENT *FROM LOCATION_TABLEGROUP BY STATEORDER BY CITYNAME[/quote] As you have only grouped by cityname so You can't use all column names in select list. replace * by column name which you are using in group by clause SELECT TOP 30 PERCENT CITYNAMEFROM LOCATION_TABLEGROUP BY STATEORDER BY CITYNAMEMon, 09 Jan 2012 10:45:40 GMTpadhisSelecting TOP PERCENT based on GROUP ?http://www.sqlservercentral.com/Forums/Topic1232636-391-1.aspxHi, I wanted to know if we can select percent of records for each group?something like :- SELECT TOP 30 PERCENT *FROM LOCATION_TABLEGROUP BY STATEORDER BY CITYNAMEMon, 09 Jan 2012 10:20:03 GMTSQL_By_Chance