[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

SQL Query - Select Top N records

Hello,

Is there a way to select top n records based on the value from the table? Basically I want to pass that "n" value using a variable.

So from example shown below, I want to select top 9 records using the query below for CompA and top 77 records for CompB and so on..

How can I do that?

I have a temp table that has value as below

Table1
Component       Caps
CompA              9
CompB              77
CompC              120
CompD              30

Query

Open in new window

Select details.Item as Barcode
,details.serial_number
, details.category
, details.manufacturer
, details.model
, details.user_component as user_component
, details.component as Purchased_by
, details.asset_status
, details.warranty_exp_date
, details.location + '/' + details.ROOM_CUBICLE as Location
, details.[owner]
, mac.UserName as LastLogon
from dbo.VW_ASSETS_DETAILS details
left join TBL_REFRESHED  refreshed on refreshed.Barcode = details.Item
left join  dbo.TBL_All_ActiveUsers  epic on details.ein = epic.ein
left join  dbo.TBL_All_MachineName mac on details.item = mac.barcode
Where category like 'SMW' and not details.location in ('Dept')
and (refreshed.refreshed like 'No' or refreshed.refreshed  is null) and asset_status = 'Deployed'
and details.manufacturer like 'Dell' and not details.model in ('E-6420','E-6320')
order by warranty_exp_date asc
0
angel7170
Asked:
angel7170
1 Solution
 
Philip PinnellCommented:
not sure how the component in Table1 relates to the query but you can do something like this

declare @top as int
select @top = Caps from Table1 where Component = 'CompA']

set rowcount @top

Select details.Item as Barcode
,details.serial_number
, details.category
, details.manufacturer
, details.model
, details.user_component as user_component
, details.component as Purchased_by
, details.asset_status
, details.warranty_exp_date
, details.location + '/' + details.ROOM_CUBICLE as Location
, details.[owner]
, mac.UserName as LastLogon
from dbo.VW_ASSETS_DETAILS details
left join TBL_REFRESHED  refreshed on refreshed.Barcode = details.Item
left join  dbo.TBL_All_ActiveUsers  epic on details.ein = epic.ein
left join  dbo.TBL_All_MachineName mac on details.item = mac.barcode
Where category like 'SMW' and not details.location in ('Dept')
and (refreshed.refreshed like 'No' or refreshed.refreshed  is null) and asset_status = 'Deployed'
and details.manufacturer like 'Dell' and not details.model in ('E-6420','E-6320')
order by warranty_exp_date asc

set rowcount 0

and that will be the top nine records for that query
0
 
lwadwellCommented:
I would try add
  row_number() over(partition by component order <some_col>) as rn
put the query in as an inline view and then join Table1 to the inline view on component and rn <= caps
0
 
Jared_SCommented:
You could do it like this...

declare @N as varchar
set @N = (select caps from #table1 where component = 'CompA')

select * from (
Select 
row_number() over (order by warranty_exp_date asc) as [rownumber],
details.Item as Barcode
,details.serial_number
, details.category
, details.manufacturer
, details.model
, details.user_component as user_component
, details.component as Purchased_by
, details.asset_status
, details.warranty_exp_date
, details.location + '/' + details.ROOM_CUBICLE as Location
, details.[owner]
, mac.UserName as LastLogon
from dbo.VW_ASSETS_DETAILS details
left join TBL_REFRESHED  refreshed on refreshed.Barcode = details.Item
left join  dbo.TBL_All_ActiveUsers  epic on details.ein = epic.ein
left join  dbo.TBL_All_MachineName mac on details.item = mac.barcode
Where category like 'SMW' and not details.location in ('950 H St','AMC/Depot', 'WHCA')
and (refreshed.refreshed like 'No' or refreshed.refreshed  is null) and asset_status = 'Deployed'
and details.manufacturer like 'Dell' and not details.model in ('E-6420','E-6320')
) a
where a.rownumber <= @N

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ralmadaCommented:
I guess you can do

declare @cap as int

select @cap = Caps from yourtempTable1 where Component = 'CompA'


Select top(@cap) details.Item as Barcode
,details.serial_number
, details.category
, details.manufacturer
, details.model
, details.user_component as user_component
, details.component as Purchased_by
, details.asset_status
, details.warranty_exp_date
, details.location + '/' + details.ROOM_CUBICLE as Location
, details.[owner]
, mac.UserName as LastLogon
from dbo.VW_ASSETS_DETAILS details
left join TBL_REFRESHED  refreshed on refreshed.Barcode = details.Item
left join  dbo.TBL_All_ActiveUsers  epic on details.ein = epic.ein
left join  dbo.TBL_All_MachineName mac on details.item = mac.barcode
Where category like 'SMW' and not details.location in ('Dept')
and (refreshed.refreshed like 'No' or refreshed.refreshed  is null) and asset_status = 'Deployed'
and details.manufacturer like 'Dell' and not details.model in ('E-6420','E-6320')
order by warranty_exp_date asc 

Open in new window

0
 
lwadwellCommented:
Is component user_component or component?  Assuming the later... the SQL would perhaps be:
select a.* from (
Select 
  row_number() over (partition by details.component order by warranty_exp_date asc) as rn
, details.Item as Barcode
, details.serial_number
, details.category
, details.manufacturer
, details.model
, details.user_component as user_component
, details.component as Purchased_by
, details.asset_status
, details.warranty_exp_date
, details.location + '/' + details.ROOM_CUBICLE as Location
, details.[owner]
, mac.UserName as LastLogon
from dbo.VW_ASSETS_DETAILS details
left join TBL_REFRESHED  refreshed on refreshed.Barcode = details.Item
left join  dbo.TBL_All_ActiveUsers  epic on details.ein = epic.ein
left join  dbo.TBL_All_MachineName mac on details.item = mac.barcode
Where category like 'SMW' and not details.location in ('950 H St','AMC/Depot', 'WHCA')
and (refreshed.refreshed like 'No' or refreshed.refreshed  is null) and asset_status = 'Deployed'
and details.manufacturer like 'Dell' and not details.model in ('E-6420','E-6320')
) a INNER JOIN #table1 t1 on a.Purchased_by = t1.component AND rn <= Caps

Open in new window

0
 
Ephraim WangoyaCommented:
Give this a try

with cte as
(
	Select 
		ROW_NUMBER() over (partition by details.user_component order by warranty_exp_date) RN
		,details.Item as Barcode
		,details.serial_number
		, details.category
		, details.manufacturer
		, details.model
		, details.user_component as user_component
		, details.component as Purchased_by
		, details.asset_status
		, details.warranty_exp_date
		, details.location + '/' + details.ROOM_CUBICLE as Location
		, details.[owner]
		, mac.UserName as LastLogon
	from dbo.VW_ASSETS_DETAILS details 
	left join TBL_REFRESHED  refreshed on refreshed.Barcode = details.Item
	left join  dbo.TBL_All_ActiveUsers  epic on details.ein = epic.ein
	left join  dbo.TBL_All_MachineName mac on details.item = mac.barcode
	Where category like 'SMW' 
	and not details.location in ('Dept')
	and (refreshed.refreshed like 'No' or refreshed.refreshed  is null) 
	and asset_status = 'Deployed'
	and details.manufacturer like 'Dell' 
	and not details.model in ('E-6420','E-6320')
)

select * 
from cte A
inner join TempTable B on (A.user_component = B.Component and A.RN <= B.Caps)

Open in new window

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!

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