Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm pulling back a list of orders but I need to only display a percentage of the records. for example we want the top 30 or top 50. The percentagbe is variable so I'd like to not use TOP. I tried the code below but I'm getting back no records.

SELECT x.SelectionCriteriaID, 
       x.RowNumber RowNumber,
  FROM (SELECT @SelectionCriteriaID SelectionCriteriaID, 
               @DeliveryCriteriaID DeliveryCriteriaID, 
               @SurveyID SurveyID, 
               ROW_NUMBER() OVER (ORDER BY ord.intcustomerid) AS RowNumber
          FROM ord
         WHERE ord.cntoid >= ISNULL(@MinNumberOrders, 1)
           AND ord.cntoid < ISNULL(@MaxNumberOrders, 999999999))x
 GROUP BY x.SelectionCriteriaID, x.DeliveryCriteriaID, x.SurveyID, x.intappid, x.intcustomerID, x.intorderid, x.RowNumber, x.cntoid
 HAVING x.rownumber < MAX(x.RowNumber)/(100/@percent)
share|improve this question
Are you using SQL Server? – Thomas Stringer Jun 22 '12 at 12:48
up vote 5 down vote accepted

You really should be using the TOP clause. This is perfectly legal (assuming you're using SQL Server):

declare @top_val int = 30

select top (@top_val) percent
from yourTable
order by <something>;

The above query will select the top 30% of the rows. As far as I can tell from your question, this satisfies your requirements and is the simplest method.

share|improve this answer
ok I feel foolish. I wasn't aware that Top could be given as a percent. Thanks Shark – Lumpy Jun 22 '12 at 12:49
@Lumpy No need to feel foolish, the parenthesis are required with a variable. By the way, is this SQL Server you are using? I'm assuming that, but it's not tagged as such. – Thomas Stringer Jun 22 '12 at 12:51
yeah sql server 2008 r2. you had it pegged. – Lumpy Jun 22 '12 at 12:58
And really add an ORDER BY when you use TOP. Unless you don't care for which results will be returned - and you only care about the number of rows returned. – ypercubeᵀᴹ Jun 22 '12 at 13:09
Thanks for the edit, @AaronBertrand! – Thomas Stringer Jun 22 '12 at 14:12

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.