?
Solved

Top 3 records in sql server by group

Posted on 2014-11-10
4
Medium Priority
?
133 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 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

770 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