Dynamically determining a Top n for Top keyword

Question

Dynamically determining a Top n for Top keyword

fanxusql

SSC Enthusiast

Points: 122
More actions
December 3, 2003 at 5:56 pm

#101785

For the following sql script in a stored procedure, is it possible to dynamically determinate the top number following the Top keyword? That means the stored procedure can run for top 10, top 50, top 100 etc. depending on the variable passed by a user. Thanks a lot
Select Top 10 drug_name, Count(drug_name)
From Orders
Group by drug_name
Order by Count(drug_name) desc

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1



DECLARE @top NVARCHAR(4) --or more if needed

DECLARE @stmt NVARCHAR(200)



SET @top = '10'

SET @stmt ='SELECT TOP '+ @top +'drug_name, Count(drug_name) From Orders Group 

by drug_name Order by Count(drug_name) desc '

EXEC sp_executeSQL @stmt

You might to read this http://www.algonet.se/~sommar/dynamic_sql.html for the negative effects of dynamic sql.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jonathan SSC-Insane Points: 20427 More actions · Answer 2



CREATE PROC p_TopDrugs @Rows int AS

SET NOCOUNT ON

SET ROWCOUNT @Rows

SELECT Drug_Name, COUNT(*)

FROM Orders

GROUP BY Drug_Name

ORDER BY COUNT(*) DESC

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

That's what I really like about your replies, Jonathan!

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

fanxusql SSC Enthusiast Points: 122 More actions · Answer 4

Thanks a lot Jonathan ! The script helps me to solve the problem. But if I want use the top 10 drug name in a sub-query, I can not use 'order by'. In addition, the outsite query will retirve more than 10 rows; so I can not set @rowcount in the strored procedure. Than you.

quote:



CREATE PROC p_TopDrugs @Rows int AS

SET NOCOUNT ON

SET ROWCOUNT @Rows

SELECT Drug_Name, COUNT(*)

FROM Orders

GROUP BY Drug_Name

ORDER BY COUNT(*) DESC

--Jonathan

fanxusql SSC Enthusiast Points: 122 More actions · Answer 5

Thank you very match, Frank. It really helps me a lot. But what shoul I do if I want to use the returned drug names in a sub query?

quote:
DECLARE @top NVARCHAR(4) --or more if needed

DECLARE @stmt NVARCHAR(200)



SET @top = '10'

SET @stmt ='SELECT TOP '+ @top +'drug_name, Count(drug_name) From Orders Group 

by drug_name Order by Count(drug_name) desc '

EXEC sp_executeSQL @stmt
You might to read this http://www.algonet.se/~sommar/dynamic_sql.html for the negative effects of dynamic sql.
Frank
http://www.insidesql.de
http://www.familienzirkus.de

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

You can execute almost all T-SQL commands dynamically.

I would write your query including your subquery first in plain T-SQL and translate it then into dynamic sql.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jonathan SSC-Insane Points: 20427 More actions · Answer 7

quote:
Thanks a lot Jonathan ! The script helps me to solve the problem. But if I want use the top 10 drug name in a sub-query, I can not use 'order by'. In addition, the outsite query will retirve more than 10 rows; so I can not set @rowcount in the strored procedure. Than you.



CREATE PROC p_TopDrugs @Rows int AS

SET NOCOUNT ON

SET ROWCOUNT @Rows

SELECT Drug_Name, COUNT(*) Cnt

INTO #t

FROM Orders

GROUP BY Drug_Name

ORDER BY COUNT(*) DESC

SET ROWCOUNT 0

SELECT ...

FROM ... x JOIN #t t ON t.Drug_Name = x.Drug_Name

WHERE ...

I have a couple of concerns, though.

You should use something not subject to change as your primary key, not the name of a drug.
Using TOP without its WITH TIES option means that your result set may be arbitrary; e.g. if you are getting the "top 10" ordered drugs and there are three drugs with the same number of orders as the tenth in the list, the information will be capricious. That's one reason why SQL sticklers like Joe Celko balk at using nonstandard extensions like TOP.

If you can live with the issues raised by the article Frank noted, then using dynamic SQL and the WITH TIES option would probably be best. I can think ways to mimic WITH TIES without using dynamic SQL, but none of them are pretty.

--Jonathan