title: RANK() with PIVOT() - limit number of ranks in output
body: <p>I am using the PIVOT function on a SELECT RANK() over(Partition by...) to return a table "matrix" showing which of my war gaming friends have the most wins per army. I want to limit the matrix to only show the top 3 ranked members per army (eg. in the "HE" column in the image below I want to exclude the highlighted record with the rank of "4")</p>
  
  <p><img src="https://lh4.googleusercontent.com/-jpCevzY9iMs/UJz3nvF1uxI/AAAAAAAASfE/ZPlPaiV_5ac/s800/MemberRanking.JPG" alt="Army Wins by Member"></p>
  
  <p>I presume I need to include a WHERE or TOP clause but cannot determine it's location. I have tried looking on this site and google but cannot find an answer. Sorry if this is a learner's question but I'm still fairly new to SQL Server.</p>
  
  <p>Here are the two tables [Armies] and [Battles] (in spreadsheet format):-
  <a href="https://docs.google.com/spreadsheet/ccc?key=0Ana40VqkvVtRdDAwc1BRWnhsWEdaaTQzcFprQmlyeVE" rel="nofollow">https://docs.google.com/spreadsheet/ccc?key=0Ana40VqkvVtRdDAwc1BRWnhsWEdaaTQzcFprQmlyeVE</a></p>
  
  <p>Here's my code:-</p>
  
  <pre><code>SELECT *
  FROM
      (
      SELECT
          RANK() over(Partition by ArmyMnemonic Order by COUNT(WDL) desc, Member) as ranks, ArmyMnemonic, Army, Member,
           COUNT(WDL) as Wins
      FROM
          [dbo].[Battles]
  
      INNER JOIN Armies on Army1 = Armies.ArmyNum
  
      Where
          WDL=2 and Home=1 -- represents a "Win"
      Group By
          Member, ArmyMnemonic, Army, WDL
      ) as rnk
  
  PIVOT (sum(rnk.ranks) for ArmyMnemonic in([Be],[Br],[DoC],[DE],[Dw],[HE],[Li],[OK],[OG],[Sk],[TE],[TK],[VC],[WoC],[WE])) as pvt
  
  ORDER BY Wins Desc;
  </code></pre>
  
  <p>Thanks for any help you can give.</p>
  
  <p>CREATE TABLE Scripts:-</p>
  
  <pre><code>USE [WFBattlesDB]
  GO
  
  /****** Object:  Table [dbo].[Armies]    Script Date: 11/09/2012 13:24:15 ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  SET ANSI_PADDING ON
  GO
  
  CREATE TABLE [dbo].[Armies](
  [ArmyNum] [int] NOT NULL,
  [ArmyMnemonic] [nvarchar](3) NOT NULL,
  [Army] [char](30) NOT NULL,
  [Official] [bit] NULL,
  [Active] [bit] NULL,
   CONSTRAINT [PK_Armies] PRIMARY KEY CLUSTERED 
  </code></pre>
  
  <p>(
      [ArmyNum] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]</p>
  
  <pre><code>GO
  
  SET ANSI_PADDING OFF
  GO
  </code></pre>
  
  <hr>
  
  <pre><code>USE [WFBattlesDB]
  GO
  
  /****** Object:  Table [dbo].[Battles]    Script Date: 11/09/2012 13:25:35 ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE TABLE [dbo].[Battles](
  [keyBattle] [int] IDENTITY(1,1) NOT NULL,
  [subDate] [datetime] NOT NULL,
  [Member] [nvarchar](20) NOT NULL,
  [Home] [bit] NOT NULL,
  [Army1] [int] NOT NULL,
  [Army2] [int] NOT NULL,
  [WDL] [int] NOT NULL,
  [PtsVal] [int] NULL,
  [MVU] [nvarchar](30) NULL,
  [Fun] [int] NULL,
  [Luck] [int] NULL,
  [Notes] [nvarchar](1500) NULL,
  [Link] [nvarchar](255) NULL
  </code></pre>
  
  <p>) ON [PRIMARY]</p>
  
  <pre><code>GO
  </code></pre>