Take the 2-minute tour ×
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.

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.DeliveryCriteriaID, 
       x.SurveyID, 
       x.intappid, 
       x.intcustomerID, 
       x.intorderid, 
       x.RowNumber RowNumber,
       x.cntoid
  FROM (SELECT @SelectionCriteriaID SelectionCriteriaID, 
               @DeliveryCriteriaID DeliveryCriteriaID, 
               @SurveyID SurveyID, 
               ord.intappid, 
               ord.intcustomerID, 
               ord.intorderid, 
               ord.cntoid,
               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
add comment

1 Answer

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
    col1,
    col2,
    col3
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
1  
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
1  
@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
3  
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
1  
Thanks for the edit, @AaronBertrand! –  Thomas Stringer Jun 22 '12 at 14:12
show 2 more comments

Your Answer

 
discard

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.