• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 138
  • Last Modified:

Top 3 records in sql server by group

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
Mikeyman_01
Asked:
Mikeyman_01
  • 2
1 Solution
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Mikeyman_01Author Commented:
yes ...That is what I mean...
0
 
Koen Van WielinkIT ConsultantCommented:
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

Open in new window

0
 
Mikeyman_01Author Commented:
Koen,

Very well done
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now