Solved

# Top 3 records in sql server by group

Posted on 2014-11-10
118 Views
item     whse       lot         qty         NumericDate
x               1          sdf        200             20141104
x               1          qwe        20             20141103
x               1          4rff       210             20141104
x               1          uyh       220             20141104
y               1          sdf        201             20141101
y               1          fgh        202             20141104
y               1          0de        203            20141104
y               1          90o       204             20141105

The answer that I need is to grab the top 3 records per Item, per Warehouse by NumericDate
if their is a tie...then the alphanumeric asc value of the lot that occurs first..

So a total of 6 rows to return...

Hopefully this I doable
0
Question by:Mikeyman_01
• 2

LVL 33

Expert Comment

ID: 40434094
Do you mean like

item     whse       lot         qty         NumericDate
x               1          sdf        200             20141104
x               1          qwe        20             20141103
x               1          4rff       210             20141104
y               1          sdf        201             20141101
y               1          fgh        202             20141104
y               1          0de        203            20141104
^--- top 3 from each group?
0

Author Comment

ID: 40434157
yes ...That is what I mean...
0

LVL 13

Accepted Solution

Koen Van Wielink earned 500 total points
ID: 40434158
Will this work? You didn't specify your sorting order very clearly, so it might need some tweaking:

``````Create table #stock
(	Item	nvarchar(12)
,	Whse	int
,	Lot		nvarchar(50)
,	Qty		int
,	NumericDate	int
)

insert into #stock
values	('x', 1, 'sdf', 200, 20141104)
,	('x', 1, 'qwe', 20, 20141103)
,	('x', 1, '4rff', 210, 20141104)
,	('x', 1, 'uyh', 220, 20141104)
,	('y', 1, 'sdf', 201, 20141101)
,	('y', 1, 'fgh', 202, 20141104)
,	('y', 1, '0de', 203, 20141104)
,	('y', 1, '90o' ,204, 20141105)

select	s.*
from	(Select	item
,	whse
,	lot
,	qty
,	NumericDate
,	row_number() over (partition by item, whse
order by item, whse, NumericDate desc, lot asc)
as 'Ranking'
from	#stock) as s
where	s.Ranking <= 3

drop table #stock
``````
0

Author Closing Comment

ID: 40437275
Koen,

Very well done
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.