Selecting Top N Records Group wise

Often we require to query to find top among subset, for example if you have a table called OderPlaced where you want the top two order for each product. There are couple of way we can actually do this, let’s check out

Problem Statement:  To find the top 2 orders for each productid based upon the earliest orderid.

Solution: Let’s create a OrderPlace table with two column OderId and ProductId

create table OrderPlace

(OrderId int,

ProductId int)

Command(s) completed successfully.

Lets insert few records in OrderPlace table

insert into OrderPlace (OrderId, ProductId) values (82, 707)

insert into OrderPlace (OrderId, ProductId) values (83, 707)

insert into OrderPlace (OrderId, ProductId) values (84, 707)

insert into OrderPlace (OrderId, ProductId) values (82, 708)

insert into OrderPlace (OrderId, ProductId) values (83, 708)

insert into OrderPlace (OrderId, ProductId) values (84, 708)

insert into OrderPlace (OrderId, ProductId) values (82, 711)

insert into OrderPlace (OrderId, ProductId) values (83, 711)

insert into OrderPlace (OrderId, ProductId) values (84, 711)

insert into OrderPlace (OrderId, ProductId) values (82, 712)

insert into OrderPlace (OrderId, ProductId) values (83, 712)

insert into OrderPlace (OrderId, ProductId) values (84, 712)

insert into OrderPlace (OrderId, ProductId) values (82, 714)

insert into OrderPlace (OrderId, ProductId) values (83, 714)

insert into OrderPlace (OrderId, ProductId) values (84, 714)

insert into OrderPlace (OrderId, ProductId) values (82, 715)

insert into OrderPlace (OrderId, ProductId) values (83, 715)

insert into OrderPlace (OrderId, ProductId) values (84, 715)

insert into OrderPlace (OrderId, ProductId) values (83, 716)

insert into OrderPlace (OrderId, ProductId) values (84, 716)

Now since we have records in place, lets design the query to find top 2 orders for each productid based upon the earliest ordered.

select OrderId,ProductId from

(select op.OrderId,op.ProductId, rowid = ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY OrderId)

from OrderPlace op) t_op

where rowid < =2

OrderId     ProductId

----------- -----------

82          707

83          707

82          708

83          708

82          711

83          711

82          712

83          712

82          714

83          714

82          715

83          715

83          716

84          716

(14 row(s) affected)

Here we gave row number by grouping ProductId then in outer query we just fetch those records which has row number >=2.

Programming is a mad work performed by genius minds