A few lines of data from 5 SQL packets.

The data in the table group, sometimes only needs the aggregate value of a column; sometimes need to return the whole line of data, the methods used include: subquery, ROW_NUMBER, APPLY, the overall feeling is ROW_NUMBER more intuitive.
Test data:

if OBJECT_ID('testGroup') is not null
drop table testGroup
GO
create table testGroup
(
ID int identity primary key,
UserID int,
OrderID int
) 
GO
insert testGroup 
select 1,10 union all
select 1,20 union all
select 1,30 union all
select 2,100 union all
select 2,200 union all
select 3,1000 union all
select 3,2000 union all
select 3,3000 union all
select 3,4000

A first line. Packet (maximum / minimum value)
1 remove a column of maximum / minimum packet, not required to display the other columns
The most common aggregation group, group by group, only in the packet / polymerization column can be displayed.

select UserID, MAX(OrderID) as MaxOrderID
from testGroup 
group by UserID

2 remove a column of maximum / minimum packet, a requirement that the other columns

To display the table the other columns, group by is not good, can use the sub query.

select * from testGroup a 
where ID = (select MAX(ID) from testGroup b where a.UserID = b.UserID)
order by ID
--Or
select * from testGroup 
where ID in (select MAX(ID) from testGroup group by UserID)
--Or
select * from testGroup as a 
where a.ID in (select top 1 ID from testGroup b where a.UserID = b.UserID order by b.OrderID desc)
--Or
select * from testGroup a
where not exists(select 1 from testGroup b where a.UserID = b.UserID and a.OrderID < b.OrderID)
--Or
select * from testGroup a
where (select count(1) from testGroup b where a.UserID = b.UserID and a.id <= b.id) = 1

Two. The first N for packet (the top several)
Before the N behavior of positive ranking (ASC), N after changed into reverse sequence (DESC), N=1 is the maximum / minimum value. The previous 2 (N=2) as an example.
1 SQL Server 2000 method
(1)The sub query

select * from testGroup as a
where a.ID in (select top 2 ID from testGroup b where a.UserID = b.UserID order by b.OrderID)
--Or
select * from testGroup a
where not exists (select 1 from testGroup b where a.UserID = b.UserID and a.OrderID > b.OrderID 
having count(1) >= 2)
--Or
select * from testGroup a
where (select count(1) from testGroup b where a.UserID = b.UserID and a.ID >= b.ID) <= 2
--No unique table, checksum can be used to identify each row
select * from testGroup as a
where checksum(*) in (select top 2 checksum(*) from testGroup b where a.UserID = b.UserID order by b.OrderID)

2 SQL Server 2005 new grammar

(2) ROW_NUMBER()

select ID, UserID, OrderID
from 
(select *, ROW_NUMBER() over(partition by UserID order by OrderID) num
from testGroup ) t
where t.num between 1 and 2

(3) APPLY(TOP)

select distinct t.* from testGroup a
cross apply (select top 2 ID, UserID, OrderID from testGroup b
where a.UserID = b.UserID order by b.OrderID) as t

Three. The N group (No. N)
The above queries, range values are changed to a fixed value, can take a specific row, next to third (N=3) as an example.
(1) The sub query

select * from testGroup a
where (select count(1) from testGroup b where a.UserID = b.UserID and a.OrderID >= b.OrderID) = 3
--Or
select * from testGroup a
where exists (select 1 from testGroup b where a.UserID = b.UserID and a.OrderID >= b.OrderID 
having count(1) = 3)

(2) ROW_NUMBER()

select ID, UserID, OrderID
from 
(select *, ROW_NUMBER() over(partition by UserID order by OrderID) num
from testGroup ) t
where t.num = 3

Posted by Bertram at November 20, 2013 - 4:19 AM