I want to use an SQL statement to retrieve the first two records of each category.

Source: Internet
Author: User
--> 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)
*/

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.