--> Generate Test Data: # T
If object_id ('tempdb. DBO. # t') is not null drop table # T
Create Table # T (ID varchar (3), Gid int, author varchar (29), title varchar (39), date datetime)
Insert into # T
Select '001', 1, 'built by the authorization', 'Go deep into sqlserver2005 Development Management and Application instance', '2017-05-10 'Union all
Select '002 ', 1, 'hu baijing', 'sqlserver2005 Performance Tuning ', '2017-03-22' Union all
Select '003 ', 1, 'groff. J. r.',' SQL full Manual', '2017-07-01 'Union all
Select '004 ', 1, 'kalendelance', 'sqlserver2005 technology insider storage engine', '2017-08-01 'Union all
Select '005 ', 2, 'Alex. kriegel. Boris. M. Trukhnov', 'SQL script', '2017-10-05' Union all
Select '006 ', 2, 'apsara technology Product Development Center', 'sqlserver2000 advanced management and developing', '2017-09-10' Union all
Select '007 ', 2, 'hu baijing', 'sqlserver2005 database development details', '2017-06-15 'Union all
Select '008 ', 3, 'chen haokui', 'sqlserver2000 stored procedure and XML programming ', '2017-09-01' Union all
Select '009', 3, 'zhao Songtao ', 'sqlserver2005 System Management records', '2017-10-01' Union all
Select '010 ', 3, 'huang zhantao', 'SQL technical Manual', '2017-01-01'
-- SQL query:
-- Group by GID to query the first two records of the latest date in each group
-- 1. When the field ID is unique:
Select * from # T as t where ID in (select Top 2 ID from # t where gid = T. GID order by date DESC)
-- 2. If the ID is not unique:
Select * from # T as t where 2> (select count (*) from # t where gid = T. GID and date> T. Date)
-- SQL Server 2005 New Method
-- 3. Use row_number () for grouping
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. Use Apply
Select distinct B .*
From # T as
Cross apply
(
Select top (2) * from # t where a. gid = GID order by date DESC
) As B
-- Result
/*
Id GID author title date
----------------------------------------------------------------------------------------------------------
003 1 Groff. J. R. SQL full manual 00:00:00. 000
004 1 kalendelaney sqlserver2005 technology insider storage engine 2008-08-01 00:00:00. 000
005 2 alex. kriegel. Boris. M. Trukhnov SQL book 00:00:00. 000
007 2 Hu baijing sqlserver2005 database development details 2008-06-15 00:00:00. 000
3 Zhao Songtao sqlserver2005 System Management transcript 00:00:00. 000
010 3 Huang zhantao SQL Technical Manual 00:00:00. 000
(6 rows affected)
*/