05. Take a few rows of data in a SQL group

Original: 05. Take a few rows of data in a SQL group

grouping data in a table, sometimes only requires the aggregate value of a column, sometimes it is necessary to return the entire row of data, commonly used methods are: Sub-query, Row_number, APPLY, the overall feeling or row_number more intuitive.
Test data:

if object_id('Testgroup') is  not NULLDrop TableTestgroupGOCreate TableTestgroup (IDint Identity Primary Key, UserIDint, OrderIDint) GOInsertTestgroupSelect 1,Ten Union  AllSelect 1, - Union  AllSelect 1, - Union  AllSelect 2, - Union  AllSelect 2, $ Union  AllSelect 3, + Union  AllSelect 3, - Union  AllSelect 3, the Union  AllSelect 3,4000

I. Take the 1th row in the group (max/min)
1. Remove the maximum/minimum value of a column in the group, no additional columns are required
The most common grouping aggregation, when grouped by group BY, only the columns that participate in grouping/aggregation can be displayed.

Select MAX  as Maxorderid  from  Group by UserID

2. Remove the maximum/minimum value of a column in the group, asking for additional columns to be displayed

To display the other columns in the table, the group by is not a good implementation, you can use subqueries.

Select *  fromTestgroup awhereId=(Select MAX(ID) fromTestgroup bwhereA.userid=B.userid)Order  byID--orSelect *  fromTestgroupwhereIdinch(Select MAX(ID) fromTestgroupGroup  byUserID)--orSelect *  fromTestgroup asawherea.IDinch(Select Top 1Id fromTestgroup bwhereA.userid=B.useridOrder  byB.orderiddesc)--orSelect *  fromTestgroup awhere  not exists(Select 1  fromTestgroup bwhereA.userid=B.userid andA.orderid<B.orderid)--orSelect *  fromTestgroup awhere(Select Count(1) fromTestgroup bwhereA.userid=B.userid anda.ID<=b.ID)= 1

Two. Take the first n rows in the group (top of the list)
The first n behavior is a forward sort (ASC), and the last n lines are reversed (DESC), which is the n=1 of the maximum/minimum value. The following previous 2 (n=2) as an example.
1. Syntax for SQL Server 2000
(1) Sub-query

Select *  fromTestgroup asawherea.IDinch(Select Top 2Id fromTestgroup bwhereA.userid=B.useridOrder  byB.orderid)--orSelect *  fromTestgroup awhere  not exists(Select 1  fromTestgroup bwhereA.userid=B.userid andA.orderid>B.orderid having Count(1)>= 2)--orSelect *  fromTestgroup awhere(Select Count(1) fromTestgroup bwhereA.userid=B.userid anda.ID>=b.ID)<= 2--tables that do not have a unique identity, you can use checksum to identify each rowSelect *  fromTestgroup asawhereChecksum*)inch(Select Top 2Checksum*) fromTestgroup bwhereA.userid=B.useridOrder  byB.orderid)

2. SQL Server 2005 New Syntax

(2) Row_number ()

Select ID, UserID, OrderID  from (Select*over byorder by OrderID) Num  from testgroup) Twherebetween1and2

(3) APPLY (TOP)

Select distinct t.* from testgroup a cross Apply (selecttop 2  from Testgroup b where = Order  by  as T

Three. Take the nth row in the group (rank nth)
The above query, the range value is changed to a fixed value, you can take a specific line, the following 3rd place (n=3) for example.
(1) Sub-query

Select *  fromTestgroup awhere(Select Count(1) fromTestgroup bwhereA.userid=B.userid andA.orderid>=B.orderid)= 3--orSelect *  fromTestgroup awhere exists(Select 1  fromTestgroup bwhereA.userid=B.userid andA.orderid>=B.orderid having Count(1)= 3)

(2) Row_number ()

Select ID, UserID, OrderID  from (Select*over byorder by OrderID) Num  from testgroup) Twhere=3