Solved

Top 3 records in sql server by group

Posted on 2014-11-10
4
106 Views
Last Modified: 2014-11-12
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
Comment
Question by:Mikeyman_01
  • 2
4 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
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

by:Mikeyman_01
ID: 40434157
yes ...That is what I mean...
0
 
LVL 12

Accepted Solution

by:
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

Open in new window

0
 

Author Closing Comment

by:Mikeyman_01
ID: 40437275
Koen,

Very well done
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now