Sql,groupby take the first three rows after each group

--Generate test data: #T
IF object_id (' tempdb.dbo. #T ') is a not NULL DROP TABLE #T
CREATE TABLE #T (ID VARCHAR (3), GID int,author varchar, Title varchar (), Date DATETIME)
INSERT into #T
SELECT ' 001 ', 1, ' Jiangjian ', ' SQLServer2005 development Management and application example ', ' 2008-05-10 ' UNION all
SELECT ' 002 ', 1, ' Hu Baijing ', ' SQLServer2005 performance tuning ', ' 2008-03-22 ' UNION all
SELECT ' 003 ', 1, ' Grove GROFF.J.R ', ' SQL Full manual ', ' 2009-07-01 ' UNION all
SELECT ' 004 ', 1, ' Kalendelaney ', ' SQLServer2005 Technology insider Storage engine ', ' 2008-08-01 ' UNION all
SELECT ' 005 ', 2, ' Alex.kriegel.boris.m.trukhnov ', ' SQL Bible ', ' 2007-10-05 ' UNION all
SELECT ' 006 ', 2, ' Freescale Technology product Development Center ', ' SQLServer2000 advanced management and Development ', ' 2007-09-10 ' UNION all
SELECT ' 007 ', 2, ' Hu Baijing ', ' SQLServer2005 database development ', ' 2008-06-15 ' UNION all
SELECT ' 008 ', 3, ' Chen Haoqui ', ' SQLServer2000 stored procedure with XML programming ', ' 2005-09-01 ' UNION all
SELECT ' 009 ', 3, ' Zhao Song ', ' SQLServer2005 system Management Record ', ' 2008-10-01 ' UNION all
SELECT ' 010 ', 3, ' Huang Jintao ', ' SQL Technical Handbook ', ' 2006-01-01 '

--sql query as follows:
--Group by GID, check the last 2 records of date in each group

--1. When the field ID is unique:
SELECT * from #T as-T where ID in (the Select TOP 2 ID from #T WHERE gid=t.gid ORDER by Date DESC)

--2. If the ID is not a momentary:
SELECT * from #T as T where 2> (select COUNT (*) from #T where Gid=t.gid and Date>t.date)

--sql Server 2005 uses the new method
--3. Using Row_number () for grouping of rows
SELECT id,gid,author,title,date
From
(
SELECT Rid=row_number () over (PARTITION by GID ORDER by Date DESC), *
From #T
) as T
WHERE rid<=2

--4. Using apply
SELECT DISTINCT b.*
From #T as a
Cross APPLY
(
SELECT TOP (2) * from #T WHERE a.gid=gid ORDER by Date DESC
) as B