Solved

Top 3 records in sql server by group

Posted on 2014-11-10
4
118 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 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

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.

776 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