XPost: microsoft.public.sqlserver.programming   
   From: genew@ocis.net   
      
        The following is my reply to a help request in an Access group.   
   My solution does work in SSE2008, but I would like to know if it is   
   possible to reduce it to one select rather than the two I used.   
      
   On Thu, 16 Aug 2012 12:25:30 -0700 (PDT), pbuscio@comcast.net wrote:   
      
   >I have a query that shows the players in my pool league ranked by winning   
   pct. what i am looking for is a new query, or changing the existing query to   
   sho the top ranked player for each team only. here is the SQL and a sample   
   output   
   >   
   >SELECT tblPlayerStats_2.Rank, tblPlayerStats_2.PlayerName, tblP   
   ayerStats_2.Team, tblPlayerStats_2.GP, tblPlayerStats_2.Won, tbl   
   layerStats_2.Lost, tblPlayerStats_2.Pct   
   >FROM tblPlayerStats_2   
   >GROUP BY tblPlayerStats_2.Rank, tblPlayerStats_2.PlayerName, tb   
   PlayerStats_2.Team, tblPlayerStats_2.GP, tblPlayerStats_2.Won, t   
   lPlayerStats_2.Lost, tblPlayerStats_2.Pct   
   >ORDER BY tblPlayerStats_2.Rank;   
   >   
   >   
   >   
   >Rank	PlayerName	Team	        GP	Won	Lost	Pct   
   >1	Ed Sheehan	Doms B	        24	21	3	0.88   
   >2	Tom Bow	        Kat A	        23	20	3	0.87   
   >3	Fran Convery	Kat B	        20	17	3	0.85   
   >3	Mike Monell	Doms B	        20	17	3	0.85   
   >5	Nick Jankowski	McMichaels	11	9	2	0.82   
   >6	John Grassia	McMichaels	24	19	5	0.79   
   >7	Mike Pryzwara	Sharkys	        19	15	4	0.79   
   >7	Tony Zubec	Riverside	19	15	4	0.79   
   >9	Warren Darnell	McMichaels	23	18	5	0.78   
   >10	John Herrmann	Riverside	24	18	6	0.75   
   >10	Nick Comstock	Sharkys	        24	18	6	0.75   
      
        I did this in SQL Server 2008 Express so there may be syntax   
   differences.  You will also have to adjust names as "rank" is a   
   reserved word in SQL Server.   
      
        This would have been easier if you had included the DDL for the   
   table and inserts for the data as I did below.  If I had not been   
   curious about a point, I would not have bothered.  (Make it easy for   
   people to help you.)   
      
   ***** Start of Code *****   
   use tempdb   
      
   drop table #Stats   
   drop table #Grouped   
      
   create table #Stats   
    (   
    theRank int not null,   
    PlayerName nvarchar(max) not null,   
    Team nvarchar(max) not null,   
    GP int not null,   
    Won int not null,   
    Lost int not null,   
    Pct numeric(4,2) not null   
    )   
      
   insert into #Stats   
    (theRank,PlayerName,Team,GP,Won,Lost,Pct)   
   values   
    (1,'Ed Sheehan','Doms B',24,21,3,0.88),   
    (2,'Tom Bow','Kat A',23,20,3,0.87),   
    (3,'Fran Convery','Kat B',20,17,3,0.85),   
    (3,'Mike Monell','Doms B',20,17,3,0.85),   
    (5,'Nick Jankowski','McMichaels',11,9,2,0.82),   
    (6,'John Grassia','McMichaels',24,19,5,0.79),   
    (7,'Mike Pryzwara','Sharkys',19,15,4,0.79),   
    (7,'Tony Zubec','Riverside',19,15,4,0.79),   
    (9,'Warren Darnell','McMichaels',23,18,5,0.78),   
    (10,'John Herrmann','Riverside',24,18,6,0.75),   
    (10,'Nick Comstock','Sharkys',24,18,6,0.75)   
      
   select Team,max(Pct) as maxPct into #Grouped from #Stats   
   group by Team   
      
   select theRank,PlayerName,Stats.Team,GP,Won,Lost,Pct from #Stats as   
   Stats   
   inner join #Grouped as Grouped   
   on Stats.Team=Grouped.Team and Stats.Pct=Grouped.maxPct   
   order by theRank   
   ***** End of Code *****   
      
   Sincerely,   
      
   Gene Wirchenko   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)