Solved

Top 3 records in sql server by group

Posted on 2014-11-10
4
126 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 34

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 13

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

729 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